Count down available stock

[frankhanhart]

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

[sunny_talwar]

May be this for AVAILABLE STOCK

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

[frankhanhart]

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...

[sunny_talwar · Accepted solution]

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;

[frankhanhart]

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 !