Cumulation on load script

[krish2459]

Hi,

How to to cumulation load script.

This expression is not working :

RangeSum(peek('Cumulative') ,ReceivedQty) AS Cumulative

LOAD
if(isnull(UPPER(po_version)),'-',UPPER(po_version)) as "Base",
UPPER(ems) as "EMS*",
UPPER(mpn) as "MPN*",
UPPER(maker) as "MAKER*",
UPPER(supplier) as "SUPPLIER*",
upper(NPI_flag) as "NPI Flag_V",
months as "MONTH*",
ReceivedQty as "Delivered Quantity",
RangeSum(peek('Cumulative') ,ReceivedQty) AS Cumulative,
BalanceQty as "Balance Quantity",
Total_RequestedQty ,
text("commodity_type") as "Commodity Type",

processed_at

resident Temp Where Left(ems, 3) <> 'PNE'and months <> 'None' ;

Could you please help me with this.

Thanks...

[krish2459]

Any help.

Thanks..

[marcus_sommer]

Within the most scenarios you need to apply an appropriate order by statement, like:

...
resident Source
order by Key1, Key2, DateField;

to ensure that the data are loaded within the expected/wanted order and you may need some if-loops around the cumulation to check if the key-value is identically to the previous record or if it has changed and the cumulation starts again at the beginning and/or if any further conditions needs to be considered. This means something like:

...
if(Key = previous(Key), rangesum(Value, peek('Cumulation')), Value) as Cumulation
...

- Marcus