2 Replies Latest reply: Jun 3, 2016 7:00 PM by Rui Esteves RSS

    How to get the last value for each period?

    Rui Esteves

      Hi,

      I want to make a report that shows the current status of each account at the end of each period.

      Unfortunately I do not have all the historical information. All I have is a bunch of records with ACCOUNT, DATE, ACCOUNT_TOTAL.

      I am trying to create a table with: PERIOD, ACCOUNT, ACCOUNT_VALUE, where:

      • PERIOD is the Year/Month, or Last day of the analysis period (Quarterly)
      • ACCOUNT is the same as the original table
      • ACCOUNT_VALUE is the last value that appears on the original table for that account with the maximum value of the DATE field but smaller or equal to the end of the PERIOD

      For example, if I have:

      TABLE1:

      ACCOUNT, DATE, ACCOUNT_TOTAL

      123, 20150212, 150

      123, 20150317,125

      123, 20150816, 180

      456, 20150110, 110

       

      And I have:

      TABLE2:

      PERIOD

      201503

      201506

      201509

      201512

       

      I want to have a resulting table with:

      RESULT_TABLE:

      PERIOD, ACCOUNT, ACCOUNT_VALUE

      201503, 123, 125

      201506, 123, 180

      201509, 123, 180

      201512, 123, 180

      201503, 456, 110

      201506, 456, 110

      201509, 456, 110

      201512, 456, 110

       

      Any ideas on how could I make it?

      I already autogenerated a table with the periods of analysis, now I need to populate with the latest value of each account for that period

       

      Thank you

      Rui

        • Re: How to get the last value for each period?
          Sunny Talwar

          Not sure why ACCOUNT 123 for PERIOD 201503 = 125 and not 150. Can you check this:

          Capture.PNG

           

          TABLE1:

          LOAD ACCOUNT,

            Date(Date#(DATE, 'YYYYMMDD')) as DATE,

            ACCOUNT_TOTAL

          Inline [

          ACCOUNT, DATE, ACCOUNT_TOTAL

          123, 20150212, 150

          123, 20150317, 125

          123, 20150816, 180

          456, 20150110, 110

          ];

           

          FINAL_TABLE:

          LOAD *,

            Date(If(ACCOUNT = Previous(ACCOUNT), Peek('DATE') - 1, Today())) as ENDDATE

          Resident TABLE1

          Order By ACCOUNT, DATE desc;

           

          DROP Table TABLE1;

           

          TABLE2:

          LOAD Date(MonthStart(Date#(PERIOD, 'YYYYMM')), 'YYYYMM') as PERIOD

          Inline [

          PERIOD

          201503

          201506

          201509

          201512

          ];

           

          IntervalMatch:

          IntervalMatch(PERIOD)

          LOAD DATE,

            ENDDATE

          Resident FINAL_TABLE;