Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

How do I iterate the interval values between two fields

I have a table having the below fields

Order:

Order NameInitialFinalPrice
ABC15100
ABC610150
ABC1115200
ABC1620250

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:

ProductQuantityUnit Price
ABC1100
ABC2100
ABC3100
ABC4100
ABC5100

Please provide a solution

1 Solution

Accepted Solutions
Highlighted

Try this

LOAD [Order Name],

     Initial + IterNo() - 1 as Quantity,

      Price as [Unit Price]

FROM ...

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

View solution in original post

9 Replies
Highlighted

Try this

LOAD [Order Name],

     Initial + IterNo() - 1 as Quantity,

      Price as [Unit Price]

FROM ...

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

View solution in original post

Highlighted
Contributor III
Contributor III

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:   

ProductDateQtyQtyCnt
A01-Jan-1451
A01-Jan-1452
A01-Jan-1453
A01-Jan-1454
A01-Jan-1455
A02-Jan-14106
A02-Jan-14107
A02-Jan-14108
A02-Jan-14109
A02-Jan-141010
A02-Jan-141011
A02-Jan-141012
A02-Jan-141013
A02-Jan-141014
A02-Jan-141015

Please help !!

Highlighted

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

Highlighted
Contributor III
Contributor III

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

Highlighted

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

Highlighted
Contributor III
Contributor III

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

Highlighted

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?

Highlighted
Contributor III
Contributor III

This is just one product.

Highlighted

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

];


Capture.PNG