topic Re: stumped and need help with calculation in QlikView

stumped and need help with calculation

Tue, 25 Apr 2017 16:37:37 GMT

I need to determine the best number of each record (of a range) by using a calculation to measure against a qty. Each record could contain a different range. My table data looks like this:

LOAD * INLINE [
Product, Qty, Min, Max
a, 140, 25, 30
b, 130, 20, 25
c, 150, 30, 40
];

So for product 'a', my result needs to be 28 (largest calculated value) based on the calculation below:

Qty	Num	Calculated result = num*floor(Qty/Num)
140	25	125
140	26	130
140	27	135
140	28	140
140	29	116
140	30	120

Hopefully, I am overthinking it. Any advice you could provide would be greatly appreciated, thank you.

Re: stumped and need help with calculation

sunny_talwar — Tue, 25 Apr 2017 16:48:49 GMT

You are looking for this in script or front end?

Re: stumped and need help with calculation

Tue, 25 Apr 2017 16:53:51 GMT

in the script, if possible.

Re: stumped and need help with calculation

Tue, 25 Apr 2017 16:54:12 GMT

the load script

Re: stumped and need help with calculation

maxgro — Tue, 25 Apr 2017 17:12:03 GMT

if I understiond your question

sum(floor(Qty / Range))

LOAD * INLINE [

Product, Qty, Min, Max

a, 140, 25, 30

b, 130, 20, 25

c, 150, 30, 40

];

AA:

LOAD

Product,

Min + IterNo() -1 as Range,

Min,

Max

Resident A

While Min + iterno() -1 <= Max;

Re: stumped and need help with calculation

Digvijay_Singh — Tue, 25 Apr 2017 18:45:51 GMT

May be like this, you may join the new table or can keep separate as per the need -

SUB CalNum (vNumber,SubQty,SubMin,SubMax)

vNum = 0;

For j= SubMin to SubMax

Let vStepNum = $(j)*floor(SubQty/$(j));

If vStepNum>vNum then

Let vNum = vStepNum;

Let vNumber = $(j);

ENDIF

ENDSUB

Source:

LOAD * INLINE [

Product, Qty, Min, Max

a, 140, 25, 30

b, 130, 20, 25

c, 150, 30, 40

];

Let vNoOfRows = NoOfRows('Source')-1;

For i = 0 to vNoOfRows

Let vNumber = 0;

Let vQty = Peek('Qty',$(i),'Source');

Let vMin = Peek('Min',$(i),'Source');

Let vMax = Peek('Max',$(i),'Source');

Let vProduct = Peek('Product',$(i),'Source');

Call CalNum(vNumber,vQty,vMin,vMax);

Temp:

Load '$(vProduct)' as Product,

$(vNumber) as Num

AutoGenerate(1);

Re: stumped and need help with calculation

Tue, 25 Apr 2017 19:12:22 GMT

Many, Many Thanks. This worked perfectly. I am officially a HUGE fan of this community.

Re: stumped and need help with calculation

Digvijay_Singh — Tue, 25 Apr 2017 22:17:34 GMT

Hi Jeff,

Looks like I misunderstood the question. Was curious to know how 32 is the correct value for product B as I see Min-Max range as 20-25.

Thanks,

Re: stumped and need help with calculation

Tue, 25 Apr 2017 22:37:00 GMT

Yes, you are correct and I did not notice the incorrect formula that was used for the result above. However I was able to use the load script with a different result formula. Note the following script:

Test1:
LOAD * INLINE [
Product, Qty, Min, Max
a, 140, 25, 30
b, 130, 20, 25
c, 150, 30, 40
];

Test2:
LOAD
Product,
FirstSortedValue(DISTINCT bundleQty,-stackH) as bestBundleQty
Group By Product
;

LOAD *,
bundleQty*floor(Qty/bundleQty) as stackH
;

load
Product,
Qty,
Min + iterNo() -1 as bundleQty
Resident Test1
While Min + iterNo() -1 <= Max;

DROP Table Test1;