Data Modelling

Qliksense_User — Tue, 07 Dec 2021 22:26:21 GMT

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.

topic Data Modelling in Catalog and Lineage

Data Modelling