Help with calculation in load script

Tue, 07 May 2013 01:42:33 GMT

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	Product	Due Date	Order Qty	Total Inventory	Allocated Batch Num
10001	APPLE	5/5/2013	100	250
10002	APPLE	6/5/2013	143	250	B100
10003	ORANGE	6/5/2013	80	500
10004	GRAPE	6/5/2013	35	185
10005	APPLE	7/5/2013	95	250
10006	GRAPE	9/5/2013	150	185
10007	ORANGE	9/5/2013	60	500
10008	ORANGE	10/5/2013	130	500
10009	GRAPE	13/5/2013	50	185	B200
10010	GRAPE	13/5/2013	50	185	B300

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

Re: Help with calculation in load script

sujeetsingh — Tue, 07 May 2013 04:25:20 GMT

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.

Re: Help with calculation in load script

Tue, 07 May 2013 04:33:17 GMT

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.

Re: Help with calculation in load script

sujeetsingh — Tue, 07 May 2013 06:44:51 GMT

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)

topic Re: Help with calculation in load script in QlikView

Help with calculation in load script

Re: Help with calculation in load script

Re: Help with calculation in load script

Re: Help with calculation in load script