Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to calculate the cumulative inventory value of IMPQTY1 for every given product (stored in the same warehouse and the same company) by adding up the closing inventory for every month of every year . I tried using the following load script and the result I get is totally messed up:
Resident FD.INVENTORY.TMP
order by COMPANY,MTRL, WHOUSE, YEARMONTH desc
NoConcatenate
FD.INVENTORY:
LOAD
COMPANY,
YEARMONTH,
FISCPRD,
PERIOD,
MTRL,
WHOUSE,
IMPQTY1,
if(peek('COMPANY')=COMPANY AND peek('MTRL')=MTRL AND peek('WHOUSE')=WHOUSE, Rangesum(Peek('Rolling.IMPQTY1')+IMPQTY1), IMPQTY1) as Rolling.IMPQTY1
Resident FD.INVENTORY.TMP1
;
You used the correct way but didnt fix the order of things,
Use order by on all the dimension in if statement as required(check their order while writing them in order by statement) and also use order for your date/month(last, after all other fields) field to sort it in ascending order.
Thank you for your comment but I am not quite sure if I got this right.
I am do order by on all the dimensions that are also in my if statement -> order by COMPANY,MTRL, WHOUSE, YEARMONTH desc
if(peek('COMPANY')=COMPANY AND peek('MTRL')=MTRL AND peek('WHOUSE')=WHOUSE, Rangesum(Peek('Rolling.IMPQTY1')+IMPQTY1), IMPQTY1) as Rolling.IMPQTY1
Also, I do YEARMONTH last and I tried asc instead of descending but the result was still wrong.
Can you please check what if the format of your yearmonth field, the reason could be that it could be formatted like
'MMM YYYY' as Jan 2023 in which it can be order perfectlt, try changin it to
YYYYMM as in 202301 in that way it will order perfectly.
Also please share a Sample shot of input and output data if this doesnt solve it.
Hi @MayaKe
I think you should use 'sortorder'.
This is my script that refered 'RollingInv.png' image you attached.
-Script
Reply, thanks!