Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Loop through Date Range and return last value available script

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:

TRANSDATEBankAccountBalance
2017-01-01A1140
2017-02-04A275
2017-02-05B1200
2017-03-07B1210

So as an example: For Key Date 2017-02-05

   

TRANSDATEBankAccountBalanceKey Date
2017-01-01A11402017-02-05
2017-02-04A2752017-02-05
2017-02-05B12002017-02-05

And for 2017-03-07

   

TRANSDATEBankAccountBalanceKey Date
2017-01-01A11402017-03-07
2017-02-04A2752017-03-07
2017-03-07B12102017-03-07

Example final table for 2 key dates:

   

TRANSDATEBankAccountBalanceKey Date
2017-01-01A11402017-02-05
2017-02-04A2752017-02-05
2017-02-05B12002017-02-05
2017-01-01A11402017-03-07
2017-02-04A2752017-03-07
2017-03-07B12102017-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

Tags (1)