topic Re: Aggregate daily data as month end figures in Scalability

Aggregate daily data as month end figures

senarath — Fri, 02 Sep 2016 09:08:20 GMT

Hi,

I have a existing data set for a month as below

Date Amount

01-Jan-2016 10

02-Jan-2016 13

03-Jan-2016 11

04-Jan-2016 12

05-Jan-2016 16

............... .....

31-Jan-2016 20

My requirement is that I want to get the aggregated amount as of month end date. because I have to link these data to another data model which has figures as at month end date only.

Required to arrive at the total for the month with the month end date in the load script

Date Amount

31-Jan-2016 356

Thanks

Senarath

Re: Aggregate daily data as month end figures

Gysbert_Wassenaar — Fri, 02 Sep 2016 09:15:19 GMT

Table1:

LOAD

Date,

MonthEnd(Date) as MonthEnd

Amount

FROM

...source...

;

Table2:

LOAD

MonthEnd,

sum(Amount) as MonthTotal

RESIDENT

Table1

GROUP BY

MonthEnd

;

Or perhaps simpler if your source data is already sorted chronologically:

LOAD

Date,

Amount,

If(MonthStart(Date) = MonthStart(Previous(Date)), RangeSum(Amount, Peek(RollingMonthTotal)), Amount) As RollingMonthTotal

FROM

...source...

;

Re: Aggregate daily data as month end figures

senarath — Fri, 02 Sep 2016 10:19:11 GMT

Many thanks gysbert ...!!!! 🙂