1 Reply Latest reply: May 20, 2016 1:07 PM by Gysbert Wassenaar RSS

    Using Calculated Field Within Same Load to Calculate Missing Data

    Riley MacDonald

      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