Qlik Community

QlikView Connectors

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to get the ending inventory

Hi,

I have a problem in getting the ending inventory report could you pleas help me on what to do. See below sample for reference.

BWART as MovementType,
if(BWART = '561', 'BegBal') as Beg_Balance,
if(BWART = '562', 'BegBalReversal') as Beg_Bal_Reversal,
if(BWART = '351', 'GoodIssuePlant') as Good_Issue_Plant,
if(BWART = '301', 'GoodsIssue') as Goods_Issue,
if(BWART = '302', 'GIReversal') as GI_Reversal,
if(BWART = '901', 'GIStockTrfPlant') as GI_Transfer_Plant,
if(BWART = '902', 'GIStockTrfPlantReversal') as GI_TPlant_Reversal,
if(BWART = '101', 'GoodsReceipt') as Goods_Receipt,
if(BWART = '102', 'GRReversal') as GR_Reversal,
if(BWART = '951', 'Sales') as Sales,

MENGE as QTY,
if(BWART = '561', MENGE, 0) as BegInv,
if(BWART = '101', MENGE, if(BWART = '302', MENGE, 0)) as Receipt,
if(BWART = '351', MENGE, if(BWART = '301', MENGE,
if(BWART = '901', MENGE, if(BWART = '102', MENGE,
if(BWART = '951', MENGE, 0))))) as Issuance,


This will be the correct output for Summary Report :
BegInventroy Receipt Issuance EndInventory

5 2 2 5
10 6 4 12
20 10 10 20

Thanks,

Jhon

Tags (1)
13 Replies
suniljain
Not applicable

How to get the ending inventory

Standard Logic is

Opening Stock + Receipt Qty - Issue Qty = As On Date Stock

You are doing this in MSEG

to built this logic in SAP you have to cumulate receipe and issue based on requirement .

and you have to avoid 309 movement.

Not applicable

How to get the ending inventory

Hi Sunil,

I am using this script (<BegInv> + <Receipt> - <Issuance>) as EndInv, to get the ending inventory but unfortunately I have encounter an error.I hope u could help me to resolve my problem.

Thanks,

Jhon

suniljain
Not applicable

How to get the ending inventory

Can you post your data in Excel.?.

Not applicable

How to get the ending inventory

Hi Sunil,

Here is my sample data. I hope you could help me to get the ending inventory.

Thanks,

Jhon

Branch_CodeMat_NumberMovement_TypeStockQTYBegInvBeg_BalanceBeg_Bal_ReversalReceiptGoods_ReceiptGR_ReversalGoods_IssueGI_ReversalIssuanceSalesShould be EndInventory
5200058BegBal11BegBal001
5200058BegBal1313BegBal0013
5200058GoodsReceipt24024GoodsReceipt240
5200058Sales1001Sales0
6200058BegBal1919BegBal0019
6200058GoodsReceipt24024GoodsReceipt240
6200058GoodsReceipt48048GoodsReceipt480
6200058Sales1001Sales0
27200058BegBal1212BegBal00
27200058Sales1001Sales
31200058BegBal1717BegBal00
31200058GoodsReceipt24024GoodsReceipt24
31200058Sales1001Sales
221200058GoodsReceipt24024GoodsReceipt24
221200058Sales1001Sales
222200058GoodsReceipt24024GoodsReceipt24
222200058Sales1001Sales
2222000581000
223200058BegBal11BegBal00
223200058BegBal33BegBal00
223200058BegBalReversal30BegBalReversal00


Not applicable

How to get the ending inventory

Hi Sunil,

Please download attachedment. I hope u would find the way to help me.

Thanks,

Jhon

suniljain
Not applicable

How to get the ending inventory

Following is the logic of FIFO Inventory Method.

Stock_Summary:
Load distinct

MATERIAL,
PLANT,
Storage_Location,
M_Date ,
SUM(Quantity_Received_Into_Valuated_Stock - Quantity_Issued_From_Valuated_Stock) as Rec_Issue_Valuated_Stock,
SUM(Receipt_Quantity_Blocked_Stock - Issue_Quantity_Blocked_Stock) as Rec_Issue_Blocked_Stock,
SUM(Quantity_Received_Into_Consignment_Stock - Quantity_Issued_From_Consignment_Stock) as Rec_Issue_Consignment_Stock,
SUM(Receipt_Quantity_Stock_In_Quality_Inspection - Issue_Quantity_Stock_In_Quality_Inspection) as Rec_Issue_Quality_Inspection_Stock,
//SUM(Issue_Quantity_Stock_In_Transit- Receipt_Quantity_Stock_In_Transit) as Rec_Issue_Intransit_Stock,
SUM(Value_Received_Into_Valuated_Stock - Value_Issued_From_Valuated_Stock) AS Rec_Issue_Value_Valuated_Stock ,
SUM(Receipt_Quantity_Stock_In_Transit) as Rec_Issue_Intransit_Stock

resident Stock_Master
group by PLANT,Storage_Location, MATERIAL,M_Date ;
drop table Stock_Master;

Stock_Summary_Index:
load
*,
recno() as d
resident Stock_Summary order by PLANT,Storage_Location, MATERIAL, M_Date;
drop table Stock_Summary;

Material_Stock:
Load
MATERIAL,
PLANT,
Storage_Location,
M_Date ,
Rec_Issue_Valuated_Stock,
Rec_Issue_Intransit_Stock,
Rec_Issue_Blocked_Stock,
Rec_Issue_Consignment_Stock,
if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Valuated_Stock)+Rec_Issue_Valuated_Stock,Rec_Issue_Valuated_Stock) as Total_Valuated_Stock,
if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Valuated_Stock)) as Opening_Valuated_Stock,
if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Blocked_Stock)+Rec_Issue_Blocked_Stock, Rec_Issue_Blocked_Stock) as Total_Blocked_Stock,
if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Consignment_Stock)+Rec_Issue_Consignment_Stock, Rec_Issue_Consignment_Stock) as Total_Consignment_Stock,
if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Rec_Issue_Intransit_Stock) as Total_Intransit_Stock,

if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Value_Valuated_Stock)+Rec_Issue_Value_Valuated_Stock, Rec_Issue_Value_Valuated_Stock) as Total_Value_Valuated_Stock

resident Stock_Summary_Index order by d;
drop field d;
drop table Stock_Summary_Index;

store Material_Stock into ..\Data\Modelled QVD\Material_Stock.qvd;
Drop Table Material_Stock;


//```````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````
Stock_Summary:
LOAD
MATERIAL,
PLANT,
Storage_Location,
M_Date ,
Rec_Issue_Valuated_Stock,
Rec_Issue_Intransit_Stock,
Rec_Issue_Blocked_Stock,
Rec_Issue_Consignment_Stock,
Total_Valuated_Stock,
Opening_Valuated_Stock,
Total_Blocked_Stock,
Total_Consignment_Stock,
Total_Intransit_Stock,
Total_Value_Valuated_Stock
FROM
[..\Data\Modelled QVD\Material_Stock.qvd]
(qvd);

RIGHT JOIN (Stock_Summary) LOAD
*,
if( previous(PLANT) = PLANT AND // If the same Product,
previous(Storage_Location) = Storage_Location AND
previous(MATERIAL) = MATERIAL ,
date(previous(M_Date)-1) // Assign the prior day
, '' ) as EndDate // Otherwise assign infinity
RESIDENT Stock_Summary
// The ORDER is important. We must group by Product, and within Product, we must read Descending -- most recent Price first.
ORDER BY PLANT,Storage_Location,MATERIAL, M_Date DESC ;


/*
Determine the lowest date in the entire transaction set.
We will use this to generate the range of dates.
*/
MinDate:
LOAD
num(min(M_Date)) as minAllDate
RESIDENT Stock_Summary
;
LET vStartDate = fieldValue('minAllDate',1)-1; // Put the mindate in a variable for later loop
DROP TABLE MinDate; // No longer need the table


/*
Generate a table of Dates from the minDate up through and including today.
*/
LET vEndDate = num(today(1)); // Generate dates through today


//-----------------------------------------------------------------------------------------------------------
StockMaster: // This will be the new pricing table

LOAD *, month(Date) as Month // Add a Month dimension to ease navigation
;
LOAD
date($(vStartDate) + IterNo(),'DD.MM.YYYY') as Date
AUTOGENERATE 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate); //OK

/*
Use IntervalMatch to JOIN PriceMaster and PriceTransaction by StartDate/EndDate range.
*/

LEFT JOIN (StockMaster) IntervalMatch(Date)
LOAD
DISTINCT M_Date,
EndDate
RESIDENT Stock_Summary; //OK


/*
JOIN the Price field to PriceMaster to allow us to drop PriceMaster and
avoid the synthetic key.
*/
LEFT JOIN (StockMaster) LOAD * RESIDENT Stock_Summary; //OK


//------------------------------------------------------------------------------------------------------------


//store StockMaster into ..\Data\Modelled QVD\Summarize_Stock_Master.qvd; //
DROP TABLE Stock_Summary;


// We don't need the StartDate & EndDate fields anymore

Not applicable

How to get the ending inventory

Hi Sunil,

Thank you for the help, I will keep you posted.

Thanks

Jhon

suniljain
Not applicable

How to get the ending inventory

It is done or what ?.

Not applicable

How to get the ending inventory

Hi Sunil,

Sorry for the late response, Yes my concern has been resolved. Thank you so much for your big help, the codes you've provide its really help me to get the Ending Inventory.

Regards,

Jhon