Accumulation Count and Grouping in Data Load Script

[Purushothaman]

Dear @hic ,

Requesting your expertise on the below;

I have a table with dates, Month Year,  Client Grouping ,Client code  Account Status.

How can I accumulate the data by throughout the Date Field by ClientCode where Account Status = A , By Grouping by Client Grouping

My End Result of Accumulation should be:

Please help!!

Thank you

[ogster1974 · Accepted solution]

This will work.

[RawData]:
LOAD
1 as _RowCount,
[dd/mm/yyyy],
Dual(Date(monthstart([dd/mm/yyyy]), 'MMM-YY'),Month([dd/mm/yyyy]) &'-'&Year([dd/mm/yyyy])) as [Month Year],
[Client Grouping],
[Client Code],
[Account Status]
FROM [lib://Community:DataFiles/Req.xlsx]
(ooxml, embedded labels, table is [Raw Data]);

[EndResultTmp]:
LOAD
[Client Grouping],
[Month Year],
Sum([_RowCount]) as [Total]

Resident [RawData]
Where [Account Status]='A'
Group By
[Client Grouping],
[Month Year]

;

Drop table [RawData];

NoConcatenate
EndResult:
Load
*,
If([Client Grouping] = Previous([Client Grouping]),
RangeSum(Peek('Accumulated_Total'), Total),
Total) as Accumulated_Total
Resident EndResultTmp
Order By [Client Grouping]
;

Drop table [EndResultTmp];

[Purushothaman]

Hi @ogster1974 ,

You are super great and helpful.

Thanks a lot!! 🙂