Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dennysetiawan
Partner - Contributor III
Partner - Contributor III

Previous function with group condition

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:

dennysetiawan_0-1637115421081.png

 

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

Please help. Thank you.

Labels (3)
1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

1 Reply
marcus_sommer

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