Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
ye wrap that around an if statement IF(item_id = peek(item_id),.....)
You can load with an orderby item_id, Snapshot_DT and then use peek function to check the previous row:
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.
ye wrap that around an if statement IF(item_id = peek(item_id),.....)
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
yup, I got there in the end thanks to your help!