Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

stumped and need help with calculation

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:

   

QtyNumCalculated result = num*floor(Qty/Num)
14025125
14026130
14027135
14028140
14029116
14030120

   

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

1 Solution

Accepted Solutions
MVP
MVP

Re: stumped and need help with calculation

if I understiond your question

sum(floor(Qty / Range))

1.jpg

A:

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;

8 Replies
MVP
MVP

Re: stumped and need help with calculation

You are looking for this in script or front end?

Not applicable

Re: stumped and need help with calculation

in the script, if possible.

Not applicable

Re: stumped and need help with calculation

the load script

MVP
MVP

Re: stumped and need help with calculation

if I understiond your question

sum(floor(Qty / Range))

1.jpg

A:

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;

Digvijay_Singh
Honored Contributor III

Re: stumped and need help with calculation

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

  Next

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);

Next

Capture.PNG

Not applicable

Re: stumped and need help with calculation

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

Digvijay_Singh
Honored Contributor III

Re: stumped and need help with calculation

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,

Not applicable

Re: stumped and need help with calculation

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;