Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table having the below fields
Order:
Order Name | Initial | Final | Price |
---|---|---|---|
ABC | 1 | 5 | 100 |
ABC | 6 | 10 | 150 |
ABC | 11 | 15 | 200 |
ABC | 16 | 20 | 250 |
I want to split my Initial and Final field (Initial and Final field values refer to the range of quantities, Price refers to the unit price of the quantites sold for the specific range) into a single field which iterates the value between 1 and 5.
Example:
Product | Quantity | Unit Price |
ABC | 1 | 100 |
ABC | 2 | 100 |
ABC | 3 | 100 |
ABC | 4 | 100 |
ABC | 5 | 100 |
Please provide a solution
Try this
LOAD [Order Name],
Initial + IterNo() - 1 as Quantity,
Price as [Unit Price]
FROM ...
While Initial + IterNo() - 1 <= Final;
Try this
LOAD [Order Name],
Initial + IterNo() - 1 as Quantity,
Price as [Unit Price]
FROM ...
While Initial + IterNo() - 1 <= Final;
Hey Sunny,
Thank you so much for the solution. I need to iterate the 'QTY' values in the below table:
PROD:
PRODUCT DATE QTY
A 01-JAN-14 5
A 02-JAN-14 10
A 03-JAN-14 5
A 04-JAN-14 12
A 05-JAN-14 3
A 06-JAN-14 5
OUTPUT:
My output table should be like this:
Product | Date | Qty | QtyCnt |
---|---|---|---|
A | 01-Jan-14 | 5 | 1 |
A | 01-Jan-14 | 5 | 2 |
A | 01-Jan-14 | 5 | 3 |
A | 01-Jan-14 | 5 | 4 |
A | 01-Jan-14 | 5 | 5 |
A | 02-Jan-14 | 10 | 6 |
A | 02-Jan-14 | 10 | 7 |
A | 02-Jan-14 | 10 | 8 |
A | 02-Jan-14 | 10 | 9 |
A | 02-Jan-14 | 10 | 10 |
A | 02-Jan-14 | 10 | 11 |
A | 02-Jan-14 | 10 | 12 |
A | 02-Jan-14 | 10 | 13 |
A | 02-Jan-14 | 10 | 14 |
A | 02-Jan-14 | 10 | 15 |
Please help !!
When does QtyCnt stop? I mean it seems like it is cumulation? Is it by Each Product? Or Each Product Each Month?
My QtyCnt should stop at the cumulative sum of the quantity which is 40. (5+10+5+12+3+5 = 40)
But this is for a single product, also for just 2 days. What I am trying to ask is that do you have more than 1 products or do you only have just one product? Also, do you only have 2 days of data or more days? how does the output change when product changes or month changes or both product and month change
The product remains the same. There will no change in the product and the date.
It will remain the same? What do you mean? I am asking that do you have only 1 product in your dashboard? Or do you have multiple products?
This is just one product.
Here you are
Table:
LOAD PRODUCT,
DATE,
QTY,
RowNo() as QtyCnt
While IterNo() <= QTY;
LOAD * INLINE [
PRODUCT, DATE, QTY
A, 01-JAN-14, 5
A, 02-JAN-14, 10
A, 03-JAN-14, 5
A, 04-JAN-14, 12
A, 05-JAN-14, 3
A, 06-JAN-14, 5
];