Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

frankhanhart
New 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

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: Count down available stock

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;

4 Replies
MVP
MVP

Re: Count down available stock

May be this for AVAILABLE STOCK

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


Capture.PNG

frankhanhart
New Contributor II

Re: Count down available stock

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

MVP
MVP

Re: Count down available stock

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

Re: Count down available stock

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 !

Community Browser