Previous function with group condition

[dennysetiawan]

Hi Expert,

I have a 3 days sales data transactions with detail like this:

Table:
LOAD * Inline
[
TRX_DATE, PRODUCT, BRANCH, SUB_BRANCH, SALES
1/1/2021, PRODUCT_A, BRANCH_1, SB001, 173
1/1/2021, PRODUCT_A, BRANCH_1, SB002, 213
1/1/2021, PRODUCT_A, BRANCH_1, SB003, 256
1/1/2021, PRODUCT_A, BRANCH_2, SB004, 85
1/1/2021, PRODUCT_A, BRANCH_2, SB005, 292
1/1/2021, PRODUCT_A, BRANCH_3, SB006, 184
1/1/2021, PRODUCT_A, BRANCH_3, SB007, 130
1/1/2021, PRODUCT_A, BRANCH_3, SB008, 176
1/1/2021, PRODUCT_B, BRANCH_1, SB002, 211
1/1/2021, PRODUCT_B, BRANCH_2, SB004, 121
1/1/2021, PRODUCT_B, BRANCH_2, SB005, 222
1/1/2021, PRODUCT_B, BRANCH_3, SB007, 210
1/2/2021, PRODUCT_A, BRANCH_1, SB001, 89
1/2/2021, PRODUCT_A, BRANCH_1, SB002, 227
1/2/2021, PRODUCT_A, BRANCH_1, SB003, 189
1/2/2021, PRODUCT_A, BRANCH_2, SB004, 291
1/2/2021, PRODUCT_A, BRANCH_2, SB005, 261
1/2/2021, PRODUCT_A, BRANCH_3, SB006, 215
1/2/2021, PRODUCT_A, BRANCH_3, SB007, 250
1/2/2021, PRODUCT_A, BRANCH_3, SB008, 210
1/2/2021, PRODUCT_B, BRANCH_1, SB002, 160
1/2/2021, PRODUCT_B, BRANCH_2, SB004, 253
1/2/2021, PRODUCT_B, BRANCH_2, SB005, 256
1/2/2021, PRODUCT_B, BRANCH_3, SB007, 71
1/3/2021, PRODUCT_A, BRANCH_1, SB001, 126
1/3/2021, PRODUCT_A, BRANCH_1, SB002, 78
1/3/2021, PRODUCT_A, BRANCH_1, SB003, 69
1/3/2021, PRODUCT_A, BRANCH_2, SB004, 54
1/3/2021, PRODUCT_A, BRANCH_2, SB005, 67
1/3/2021, PRODUCT_A, BRANCH_3, SB006, 218
1/3/2021, PRODUCT_A, BRANCH_3, SB007, 89
1/3/2021, PRODUCT_A, BRANCH_3, SB008, 210
1/3/2021, PRODUCT_B, BRANCH_1, SB002, 208
1/3/2021, PRODUCT_B, BRANCH_2, SB004, 273
1/3/2021, PRODUCT_B, BRANCH_2, SB005, 152
1/3/2021, PRODUCT_B, BRANCH_3, SB007, 255
];

From that data, I want to see previous sales 1 day and previous sales 2 days based on PRODUCT, BRANCH, and SUB_BRANCH. So, the result should be like this:

I want to do this in load script. It this possible?

Please help. Thank you.

[marcus_sommer · Accepted solution]

Yes, such tasks could be solved with interrecord-functions like previous() or peek(). Important for it is that the load is properly sorted with an appropriate order by statement - which also requires a resident-load or a select-load. In your case it looks that you couldn't get this sorting with your 4 single-fields else you will need to combine them like:

sub & '|' & branch & '|' & product & '|' & date as OrderField

and within the final load you will need something like this:

if(date = previous(date) and branch = previous(branch) ..., previous(sales), 0) ...

This means the belonging records following direct on each other so that you could with previous() directly access the ancestor record whereby you will need some if-loops to detect when comes a different key which hasn't an ancestor and/or if there are further requirements of conditions or any exception-handling.

- Marcus