Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
frankhanhart
Contributor II
Contributor II

Count down available stock

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

1 Solution

Accepted Solutions
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;

View solution in original post

4 Replies
sunny_talwar

May be this for AVAILABLE STOCK

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


Capture.PNG

frankhanhart
Contributor II
Contributor II
Author

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

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
Contributor II
Contributor II
Author

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 !