3 Replies Latest reply: May 7, 2013 2:49 AM by sujeet singh RSS

    Help with calculation in load script

      Hi everyone first time poster here I usually try and search up my own answers, but i've come across a problem that I haven't been able to solve yet and hoping for some help here.

       

      Essentially i'm trying to calculate whether a product has sufficient stock in inventory in a load script, it needs to be part of the load script as the result of this calculation (sufficient stock, insufficient stock) will be used as dimensions for analysis.

       

      As a watered down example, an input table (as a csv) is of the format:

       

       

      Order Num
      ProductDue DateOrder QtyTotal InventoryAllocated Batch Num
      10001APPLE5/5/2013100250
      10002APPLE6/5/2013143250B100
      10003ORANGE6/5/201380500
      10004GRAPE6/5/201335185
      10005APPLE7/5/201395250
      10006GRAPE9/5/2013150185
      10007ORANGE9/5/201360500
      10008ORANGE10/5/2013130500
      10009GRAPE13/5/201350185B200
      10010GRAPE13/5/201350185B300

       

      Note, total inventory comes in on every order line, but is specific to a product, so there's 250 units of apple in the total inventory.

      If a order has an allocated batch number, then it's considered allocated.

      Orders are sorted by date.

       

      The new calculated field will be called 'Achievable', and values will be 'Sufficient Stock' and 'Insufficient Stock'

       

      By default, anything that has been allocated is achievable.

       

      For anything else, the order is acheivable if:

       

      the total inventory minus total allocated stock minus all unallocated orders for that item that occur before the current order is greater than the order quantity of that line.

       

      Some examples to explain the logic:

       

      For Order 10005:

      Product = Apple

      Order Qty = 95

      Total Inventory of Apple = 250

      Total Allocated Apple = 143

      Total unallocated orders of apple that come before order 10005 = 100

       

      Effectively, there's 250 - 143 - 100 = 7 apple available, and therefore order 10005 cannot be fufilled.

       

       

      For order 10004:

      Product = Grape

      Order Qty = 35

      Total inventory of grape = 185

      Total allocated grape = 100

      Total unallocated orders for grape that come before order 10004 = 0

       

      Effectively there's 185 - 100 - 0 = 85 grape available, therefore order 10004 can be fufilled.

       

       

      I hope i posted up enough information for someone to understand what i'm trying to achieve.

       

      Thanks in advance for any assistance.

      John