Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
In my scenario I have to find each line_item's actual date. For instance line_item = 01 ;
It's quantity is 10 in EKET table.
Actual date should be 05.10.2019 in EKES table since cumulative quantity is 10 in that date.
02 line item's actual date should be 22.10.2019.
EKET :
EBELN | EBELP | LINE_ITEM | Quantity | Commitment_Date
100 0001 01 10 01.10.2019(dd.mm.yyyy)
100 0001 02 20 09.10.2019
EKES:
EBELN | EBELP | DELIVERY_ITEM | Quantity | Actual_DATE
100 0001 01 1 01.10.2019
100 0001 02 7 03.10.2019
100 0001 03 2 05.10.2019
100 0001 04 10 09.10.2019
100 0001 05 5 21.10.2019
100 0001 06 5 22.10.2019
Try this:
Script:
tempEKET: //Replace this with your production data
load * inline [
EBELN,EBELP,LINE_ITEM,Quantity,Commitment_Date
100,0001,01,10,01.10.2019
100,0001,02,20,09.10.2019
];
NoConcatenate
EKET:
load EBELN,EBELP,LINE_ITEM,Quantity as QuantityEKET,Date#(Commitment_Date,'DD.MM.YYYY')as Commitment_Date,
if(RowNo()=1,Quantity,IF(EBELN=PEEK(EBELN) AND EBELP =PEEK(EBELP),Quantity+PEEK(TOTQuantity),Quantity)) as TOTQuantity
RESIDENT tempEKET;
drop table tempEKET;
tempEKES: //Replace this with your production data
load * inline [
EBELN,EBELP,DELIVERY_ITEM,Quantity,Actual_DATE
100,0001,01,1,01.10.2019
100,0001,02,7,03.10.2019
100,0001,03,2,05.10.2019
100,0001,04,10,09.10.2019
100,0001,05,5,21.10.2019
100,0001,06,5,22.10.2019
];
EKES:
NoConcatenate
load EBELN,EBELP,DELIVERY_ITEM,Quantity AS QuantityEKES,
IF(RowNo()=1,Quantity,IF(EBELN=PEEK(EBELN) AND EBELP =PEEK(EBELP),Quantity+PEEK(TOTQuantity),Quantity)) as TOTQuantity,
Date#(Actual_DATE,'DD.MM.YYYY')as Actual_DATE
resident tempEKES;
DROP TABLE tempEKES;
left join(EKET)
LOAD * Resident EKES;
DROP TABLE EKES;
exit script;
Thanks and regards,
Arthur Fong