Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
Please find my code as below
Temp_Open:
LOAD
if(not WildMatch(Replace(LTrim(Replace(Material, '0',' ')),' ', '0'),'3*','4*'),
Replace(LTrim(Replace(Material, '0',' ')),' ', '0')) as Material,
'Inventory' as Spend_Type,
Plant,
"Opening Stock Qty",
Date(Date#("Trans. date", 'YYYYMMDD')) as Posting_Date_Stock
FROM path
(qvd) where Plant='N201' and Material='108000000021' ;
Concatenate(Consumption)
LOAD
Plant ,
Spend_Type,
Material ,
Posting_Date_Stock as Posting_Date ,
"Opening Stock Qty" as "Opening Stock Qty1"
Resident Temp_Open;
Drop Table Temp_Open;
/**************** Opening Stock Logic - Stage1 ****************/
qualify *;
NoConcatenate
Temp_OpenClose:
Load
%Key_OpenClose1 , Material , Plant , Posting_Date,
Sum( "Opening Stock Qty1" + Stock_Receipt - Stock_Issued) as Stock_Close,
Alt(Sum("Opening Stock Qty1"),0) as Stock_Open,
Sum (Stock_Issued) as Stock_Issued2,
Sum (Stock_Receipt) as Stock_Receipt2
Resident Consumption Where Spend_Type = 'Inventory'
Group By Material, Plant, Posting_Date, %Key_OpenClose1;
Unqualify *;
/**************** Opening Stock Logic - Stage2 ****************/
Concatenate(Consumption)
Open_Close_Stock:
Load *,
(alt((Stock_Open1),0) + alt((Stock_Receipt1),0)-alt((Stock_Issued1),0))as Stock_Close1;
Load Distinct
Temp_OpenClose.Stock_Receipt2 as Stock_Receipt1,
Temp_OpenClose.%Key_OpenClose1 as %Key_OpenClose1,
Temp_OpenClose.Stock_Issued2 as Stock_Issued1,
Temp_OpenClose.Plant as Plant,
Temp_OpenClose.Material as Material,
Temp_OpenClose.Posting_Date as Posting_Date,
If(Temp_OpenClose.Stock_Open,Temp_OpenClose.Stock_Open,Peek(Stock_Close1)) as Stock_Open1
Resident Temp_OpenClose
Order By Temp_OpenClose.Material, Temp_OpenClose.Plant, Temp_OpenClose.Posting_Date;
Drop Table Temp_OpenClose;
exit Script;
From the Temp_Open table I am getting Stock_Open value for 1/1/2018. I need to build a logic for remaining dates.
I am concatenating it with Consumption table in which I have calculated my Stock Issue and stock Receipt.
In the Temp_OpenClose: table I am generating the first level of Stock_Open and Stock_Cose logic.
I make Alt(Sum("Opening Stock Qty1"),0) as my Stock_Open which is nothing but Stock open value for 1/1/2018 for all materials
my stock_close logic is Sum( "Opening Stock Qty1" + Stock_Receipt - Stock_Issued)
%Key_OpenClose1 is basically my key made using Plant,Material,Date and Inventory tag which i will use further in my code
Now in my Open_Close_Stock table i build the final logic for stock_open and stock_close
basically if my stock_open value for material A on 1/1/2018 is 10 and receipt value is 10 and issue value is 5
them my stock close is stocke_open+receipt- issue so 10+10-5=15 becomes my stock close for material A on 1/1/2018
now for 1/2/2018 for material A stock_open should be previous day stock_close i.e. 15 and then stock close is stocke_open+receipt- issue so 15+ receipt as on 1/2/2018 - issue as on 1/2/2018.
Using the above code i am able to get the below output
my value of stock_open1 and stock_close1 on 1/1/2018 is correct
even stock_close1 is working fine functionally because as on 1/2/2018 it is giving 0+540.08-0=540.08
but i am not able to generate stock_open for remaining dates.
Sorry for the long post. Please help.
Thanks in advance.