Detect and Evaluate changes in snapshot in Load Script

[Touyets]

Hi all,

I have a load script that is taking data from SQL in a snapshot format. So we have a table where for our items like this:

ITEM_ID	SNAPSHOT_DT	Amount	Flag: Change in amount?	Change in Amount	Total Value	Flag: Change in total value?	Change in total value
1	22/06/2019	2	Y	+1	$100	Y	+$25
1	15/06/2019	1	N	0	$75	N	$0
1	08/06/2019	1	N	0	$75	N	$0
2	22/06/2019	10	N	0	$350	N	$0
3	22/06/2019	5	Y	-15	$250	Y	-$550
3	15/06/2019	20	N	0	$800	N	$0

I marked in the red the columns I wish to create. So for example, the first 3 rows belong to the same item ID ordered by Snapshot date and you can see that item's amount and value change over time.

How do I have each row look for its predecessor and see if that item has changed in either amount or value and then calculate the change in the QS load script editor?

[LeHaoNguyen]

You can load with an orderby item_id, Snapshot_DT and then use peek function to check the previous row:

https://help.qlik.com/en-us/sense/1.0/Subsystems/WorkingWith/Content/Scripting/InterRecordFunctions/...

[Touyets]

Would that not take the previous record regardless of the Item_ID? So Item 1 might end up comparing its line to the previous row even though that previous row is for a different item? I haven't seen any filtering options for the peek function so far.

[LeHaoNguyen · Accepted solution]

ye wrap that around an if statement IF(item_id = peek(item_id),.....)

[Touyets]

Actually... I could combine 2 peek formulas and an IF() statement. have it check if the previosu row is for the same ID using peek and then it could take the previous value...

IF (PEEK(ITEM_ID)<>ITEM_ID,NULL, PEEK(VALUE) - VALUE) AS ChangeInValue

[Touyets]

yup, I got there in the end thanks to your help!