Intra-group Inter-row Calculation
Problem description & analysis
data.xlsx records dates when events under different users happen. The data is ordered by USER ID and DATE. Below is part of the file:
| A |
B |
C |
|
| 1 |
USER ID |
EVENT ID |
DATE |
| 2 |
1 |
4 |
2020-01-01 |
| 3 |
1 |
5 |
2020-01-05 |
| 4 |
1 |
6 |
2020-01-13 |
| 5 |
2 |
7 |
2020-01-03 |
| 6 |
2 |
8 |
2020-01-05 |
| 7 |
2 |
9 |
2020-01-06 |
We want to add a calculated column isTrue according to a specific rule. For the same USER ID, the first event is always true; from the second event on, if the interval between this event and the last true event is 10 or more days, the event is true, otherwise it is false. The expected result should be as follows:
| A |
B |
C |
D |
|
| 1 |
USER ID |
EVENT ID |
DATE |
isTrue |
| 2 |
1 |
4 |
2020-01-01 |
1 |
| 3 |
1 |
5 |
2020-01-05 |
0 |
| 4 |
1 |
6 |
2020-01-13 |
1 |
| 5 |
2 |
7 |
2020-01-03 |
1 |
| 6 |
2 |
8 |
2020-01-05 |
0 |
| 7 |
2 |
9 |
2020-01-06 |
0 |
The task requires inter-row calculations on each group, which involves value assignment to the temporary variable and its change.
Solution
Below is the esProc script:
| A |
|
| 1 |
=file("data.xlsx").xlsimport@t() |
| 2 |
=A2.derive(if('USER ID'!='USER ID'[-1],(t=DATE,1),if(DATE-t>=10,(t=DATE,1),0)):isTrue) |
| 3 |
=file("result.xlsx").xlsexport@t(A2) |
A2: If USER ID in the current row is different from that in the last row, the current event is the first one in its group. In this case, assign 1 to isTrue and the temporary value t is equal to the current DATE. If USER ID in the current row is the same as that in the last row, an intra-group calculation is needed, which uses the temporary variable and the current DATE to calculate the interval and assign 1 to isTrue if the result is equal to or greater than 10, and modify the value of temporary variable t to the current DATE.
https://stackoverflow.com/questions/63838362/date-diff-between-many-rows-in-excel
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
