4 Replies Latest reply: Dec 6, 2017 9:13 AM by Frank Hanhart RSS

    Count down available stock

    Frank Hanhart

      Hello, I just started with Qliksense Scripting and one of my assignments is to create a report of sales orders and if these sales orders amount are in stock and if so, how much.

       

      For simplicity I illustrate two tables below. A table which holds the amount of stock per product (fruits in this case), and a table with order lines, which hold the ordered amount of said fruit.

       

      What I want to achieve is to add a column to the ORDERS table and add for each line the amount of available stock for that order. If the amount of stock exceeds the amount ordered for that line, it should be capped to the ordered amount.

       

      Technically the available stock should be reduced with the amount ordered and 'reserved' in previous rows. For the life of me, I can't figure out how to achieve this. I would greatly appreciate it if someone could point me in the right direction.

       

      STOCK:

      LOAD * INLINE [

          PRODUCT, STOCK

          BANANA, 12

          APPLE, 0

      ];

       

      ORDERS:

      LOAD * INLINE [

          ORDERNO, PRODUCT, ORDEREDAMOUNT    

          Ord1, BANANA, 1

          Ord2, BANANA, 6

          Ord3, BANANA, 7

          Ord4, BANANA, 2

          Ord1, APPLE, 6

          Ord2, APPLE, 3

          Ord7, APPLE, 1

          Ord8, APPLE,4

      ];

       

      The result I want to achieve, should look like this :

      ORDERNO     PRODUCT     ORDEREDAMOUNT     AVAILABLE STOCK

      Ord1               BANANA          1                                   1

      Ord2               BANANA          6                                   6     (12 - 1) Still stock to fulfill this order

      Ord3               BANANA          7                                   5     (12 - 1 - 6) Remaining stock of 5 should be assigned to this order

      Ord4               BANANA          2                                   0     (12 - 1 - 6 - 5) (No stock available, show zero stock)

      etc. etc.for APPLES

        • Re: Count down available stock
          Sunny Talwar

          May be this for AVAILABLE STOCK

          RangeMax(RangeMin(ORDEREDAMOUNT, RangeSum(STOCK, -RangeSum(Above(ORDEREDAMOUNT, 1, RowNo())))), 0)


          Capture.PNG

          • Re: Count down available stock
            Frank Hanhart

            Hi Sunny, thanks for your speedy answer and help ! I see the logic and trying to replicate it within QlikSense Script, but it doesn't support the ABOVE function , which is only a chart function...

              • Re: Count down available stock
                Sunny Talwar

                Try this in the script

                 

                ORDERS:

                LOAD * INLINE [

                    ORDERNO, PRODUCT, ORDEREDAMOUNT   

                    Ord1, BANANA, 1

                    Ord2, BANANA, 6

                    Ord3, BANANA, 7

                    Ord4, BANANA, 2

                    Ord1, APPLE, 6

                    Ord2, APPLE, 3

                    Ord7, APPLE, 1

                    Ord8, APPLE,4

                ];

                 

                Left Join(ORDERS)

                LOAD * INLINE [

                    PRODUCT, STOCK

                    BANANA, 12

                    APPLE, 0

                ];

                 

                FinalTable:

                LOAD *,

                RangeMax(RangeMin(ORDEREDAMOUNT, RangeSum(STOCK, If(PRODUCT = Previous(PRODUCT), -Peek('CUMSTOCK'), 0))), 0) as AVAILABLESTOCK;

                LOAD *,

                If(PRODUCT = Previous(PRODUCT), RangeSum(Peek('CUMSTOCK'), ORDEREDAMOUNT), ORDEREDAMOUNT) as CUMSTOCK

                Resident ORDERS

                Order By PRODUCT, ORDERNO;

                 

                DROP Table ORDERS;

              • Re: Count down available stock
                Frank Hanhart

                Sunny,

                 

                you're a rockstar ! That was exactly what I'm looking for and have implemented it already in my report and the result is perfect.

                 

                Thank you so much for your speedy response and assistance, kudo's !