Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
You are looking for this in script or front end?
in the script, if possible.
the load script
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;
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
Many, Many Thanks. This worked perfectly. I am officially a HUGE fan of this community.
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,
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;