Using Calculated Field Within Same Load to Calculate Missing Data

[rileymd88]

Hi All,

I have one table which looks like this (ordered by Product&Day field):

sales_shipments

Day	Product	Product&Day	Sales	Shipments to Stores	Net Inventory
01/01/2016	Product A	Product A01/01/2016	10	100	90
01/01/2016	Product A	Product A01/01/2016	2	0	-2
01/01/2016	Product B	Product B01/01/2016	4	50	46
01/01/2016	Product B	Product B01/01/2016	5	40	35

The table is essentially a complete historical list of all shipments made to the stores and all sales from the stores. The problem I have is that we do not know what the inventory amount is per day in the stores so I would like to calculate this in Qlikview. Until now I have tried this in my load script but it is telling me the field SOH is not available. I understand that this is because within load scripts you can't reference calculated fields with the same load.

sales_shipments_inventory:

Load

Day,

Product,

Product&Day,

Sales,

[Shipment to Stores],

[Net Inventory],

If(RecNo() = 1, [Net Inventory], If(Previous(Product) = Product, Previous(Stock) + [Net Inventory], [Net Inventory])) as Stock

FROM sales_shipments;

Does anyone have any other ideas on how I can solve this?

Thanks,

R

[Gysbert_Wassenaar]

Something like this should do the trick:

Temp:

Load

     Day,

     Product,

     Product&Day,

     Sales,

     [Shipment to Stores],

     [Net Inventory]

FROM sales_shipments;

sales_shipments_inventory:

LOAD

     *,

     rangesum([Net Inventory],  If(Product=Previous(Product), peek(Stock))) as Stock

RESIDENT

     Temp

ORDER BY

     Product, Day

     ;

DROP TABLE Temp;