Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
rileymd88
New Contributor II

Using Calculated Field Within Same Load to Calculate Missing Data

Hi All,

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

sales_shipments

DayProductProduct&DaySalesShipments to StoresNet Inventory
01/01/2016Product AProduct A01/01/20161010090
01/01/2016Product AProduct A01/01/201620-2
01/01/2016Product BProduct B01/01/201645046
01/01/2016Product BProduct B01/01/201654035

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

1 Reply

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

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;



talk is cheap, supply exceeds demand
Community Browser