0 Replies Latest reply: May 5, 2017 3:49 PM by JOHN B RSS

    Loop through Date Range and return last value available script

    JOHN B

      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