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: 
Not applicable

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

3 Replies
sujeetsingh
Master III
Master III

Hi John,

First of all if you are using sql statement then plz use functions like ROLLUP()  and you can achieve this structure in sql only and if you want it to be done in Qlikview then use group by in load.

Not applicable
Author

Hi Son of Sardar,

Thanks for your input.

I am not using SQL, just qlikview.

I have tried grouping, however that only gives me the total. Which is fine for totalling allocated stock, but I haven't been successful in getting it to do the cumulative total of orders for a product that preceed this order. I suspect some sort of range sum may be the answer, but I'm inexperienced in using that function.

sujeetsingh
Master III
Master III

John.

If you are searching for cummalative sum then go to this sample well  may it help you.

you can use rangesum() function as

RangeSum(Sales,peek(date ),date)