stumped and need help with calculation

[#1]

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.

[sunny_talwar]

You are looking for this in script or front end?

[#3]

in the script, if possible.

[#4]

the load script

[maxgro · Accepted solution]

if I understiond your question

sum(floor(Qty / Range))

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]

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

[#7]

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

[Digvijay_Singh]

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,

[#9]

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;