topic Re: How do I iterate the interval values between two fields in QlikView

How do I iterate the interval values between two fields

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How do I iterate the interval values between two fields

sunny_talwar — Fri, 25 May 2018 15:17:09 GMT

Try this

LOAD [Order Name],

Initial + IterNo() - 1 as Quantity,

Price as [Unit Price]

FROM ...

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

Re: How do I iterate the interval values between two fields

Anonymous — Mon, 28 May 2018 13:30:36 GMT

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

Re: How do I iterate the interval values between two fields

sunny_talwar — Mon, 28 May 2018 21:36:25 GMT

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

Re: How do I iterate the interval values between two fields

Anonymous — Tue, 29 May 2018 05:04:20 GMT

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

Re: How do I iterate the interval values between two fields

sunny_talwar — Tue, 29 May 2018 11:03:54 GMT

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

Re: How do I iterate the interval values between two fields

Anonymous — Tue, 29 May 2018 13:11:51 GMT

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

Re: How do I iterate the interval values between two fields

sunny_talwar — Tue, 29 May 2018 13:13:00 GMT

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?

Re: How do I iterate the interval values between two fields

Anonymous — Tue, 29 May 2018 13:15:00 GMT

This is just one product.

Re: How do I iterate the interval values between two fields

sunny_talwar — Tue, 29 May 2018 13:23:52 GMT

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

];