Hi Qlik Gurus
I am trying to figure out a script to achieve the following:
Based on a key date, return the last available balance by unique account and add the key date as an additional field.
This is an example of the current data:
| TRANSDATE | Bank | Account | Balance |
| 2017-01-01 | A | 1 | 140 |
| 2017-02-04 | A | 2 | 75 |
| 2017-02-05 | B | 1 | 200 |
| 2017-03-07 | B | 1 | 210 |
So as an example: For Key Date 2017-02-05
| TRANSDATE | Bank | Account | Balance | Key Date |
| 2017-01-01 | A | 1 | 140 | 2017-02-05 |
| 2017-02-04 | A | 2 | 75 | 2017-02-05 |
| 2017-02-05 | B | 1 | 200 | 2017-02-05 |
And for 2017-03-07
| TRANSDATE | Bank | Account | Balance | Key Date |
| 2017-01-01 | A | 1 | 140 | 2017-03-07 |
| 2017-02-04 | A | 2 | 75 | 2017-03-07 |
| 2017-03-07 | B | 1 | 210 | 2017-03-07 |
Example final table for 2 key dates:
| TRANSDATE | Bank | Account | Balance | Key Date |
| 2017-01-01 | A | 1 | 140 | 2017-02-05 |
| 2017-02-04 | A | 2 | 75 | 2017-02-05 |
| 2017-02-05 | B | 1 | 200 | 2017-02-05 |
| 2017-01-01 | A | 1 | 140 | 2017-03-07 |
| 2017-02-04 | A | 2 | 75 | 2017-03-07 |
| 2017-03-07 | B | 1 | 210 | 2017-03-07 |
My initial thoughts are to create a unique id for Bank+Account and then use a loop to iterate through the key dates and pass all results to a table. My scripting knowledge is very basic so I am struggling getting syntax correct. I do know that all my dates are correctly formatted.
Concept code (is that even a thing?) basically, I know this won't work as is, but this is what I am trying to do:
Let vStartDate=Min(TRANSDATE)
Let vEndDate=Max(TRANSDATE)
Load
TRANSDATE,
[Bank],
[Account],
[Balance],
[Bank]&[Account] as [UNIQUEID]
For x= $(vStartDate) to $(vEndDate)
Return last available balance for Distinct [UNIQUEID] where TRANSDATE<= x
Load x as [KEY_DATE]
Next x
Any help would be greatly appreciated!
-JB
