Using Calculated Field Within Same Load to Calculate Missing Data

rileymd88 — Fri, 20 May 2016 09:22:34 GMT

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,

Re: Using Calculated Field Within Same Load to Calculate Missing Data

Gysbert_Wassenaar — Fri, 20 May 2016 17:07:52 GMT

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;

topic Using Calculated Field Within Same Load to Calculate Missing Data in QlikView

Using Calculated Field Within Same Load to Calculate Missing Data

Re: Using Calculated Field Within Same Load to Calculate Missing Data