How to get the last value for each period?

[#1]

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

[sunny_talwar · Accepted solution]

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

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;

[#3]

Sunny,

TY. It worked perfectly.

The reason for reducing the value (and I made sure it was in the data set example) is that sometimes some credits can reduce the value of the account temporarly.

Thank you again.