How do I iterate the interval values between two fields

[Anonymous]

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

[sunny_talwar · Accepted solution]

Try this

LOAD [Order Name],

     Initial + IterNo() - 1 as Quantity,

      Price as [Unit Price]

FROM ...

While Initial + IterNo() - 1 <= Final;

[Anonymous]

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 !!

[sunny_talwar]

When does QtyCnt stop? I mean it seems like it is cumulation? Is it by Each Product? Or Each Product Each Month?

[Anonymous]

My QtyCnt should stop at the cumulative sum of the quantity which is 40. (5+10+5+12+3+5 = 40)

[sunny_talwar]

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

[Anonymous]

The product remains the same. There will no change in the product and the date.

[sunny_talwar]

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?

[Anonymous]

This is just one product.

[sunny_talwar]

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

];