topic Help me out to understand the below script with IterNo() in QlikView

Help me out to understand the below script with IterNo()

Thu, 31 Mar 2011 07:42:23 GMT

This script I have found in a example application. Though it works per my requirement I want to know how does this script work so that in future if any changes are required then I can do it myself instead of depending on some one to proceed further.

Can some one help me out to understand the below script as I could get enough details from the tutorial regarding iterno(). So I could understand what exactly this script(Bold letters!) does.


Data:
LOAD A1,
 A2,
 A3,
 A4,
 A5,
 [1],
 Datee,
 StartDate,
 EndDate
FROM
C:\Sample5.xlsx
(ooxml, embedded labels, table is Sheet1);
Calendar:
LOAD DISTINCT
 DATE(StartDate+IterNo()-1) AS date
RESIDENT Data
WHILE StartDate+IterNo()-1<=EndDate;

Thanks in advance!

Help me out to understand the below script with IterNo()

Thu, 31 Mar 2011 08:01:53 GMT

Hi,

This script will create a date for each day between StartDate and EndDate, and that for each line of the Data table.

Help me out to understand the below script with IterNo()

Thu, 31 Mar 2011 08:11:54 GMT

Ok, more details :

The while clause will keep on loading a record while the condition is false. In your example, the first line of table Data will be loaded, IterNo (for iteration) will be 1, so StartDate-IterNo+1 will be start date, and as this is below EndDate, this first record will be loaded a second time, with IterNo = 2, and so on until EndDate. The second record of table date will be processed in the same way.

To better understand this try a simple script like :

Data:
load * inline [
StartDate, EndDate, id
1, 5, a
3, 8, b
2, 4, c
12, 14, d
];

Calendar:
LOAD DISTINCT (StartDate+IterNo()-1) AS date, id as id2, IterNo() as IterNo, rowNo() as rowNo
RESIDENT Data
WHILE StartDate+IterNo()-1<=EndDate;

Help me out to understand the below script with IterNo()

Thu, 31 Mar 2011 08:12:08 GMT

Hi Attitude ,

This function is only meaningful if used together with a while clause. IterNo( ) returns an integer indicating for which time one single record is evaluated in a load statement with a while clause. The first iteration has number 1.

For Example Your Fields Havng the example data:

StartDate EndDate

1/1/2011 3/1/2011

2/1/2011 4/1/2011

3/1/2011 4/1/2011

for the firat iteration ( loading first row), the ItrNo() returns 1, for the second row itrNo() returns 2.. and so on..

so, DATE(StartDate+IterNo()-1) AS date

means DATE(StartDate+1-1) AS date --> next date.. finally the reulatant table like this...

StartDate EndDate DATE(StartDate+1-1) <= EndDate Date

1/1/2011 3/1/2011 DATE(1/1/2011 +1-1) 1/1/2011 <=3/1/2011 true 1/1/2011

2/1/2011 4/1/2011 DATE(2/1/2011 +2-1) 3/1/2011<=4/1/2011 true 3/1/2011

3/1/2011 4/1/2011 DATE(3/1/2011 +3-1) 5/1/2011<= 4/1/2011 false -

Help me out to understand the below script with IterNo()

Fri, 01 Apr 2011 06:00:00 GMT

Hi Renaud & Arun

Thanks a lot for your help and time. I am able to understand very clearly about IterNo() now hope I can use it in my application without any doubt :-).

Special thanks to arun for explaining the use of IterNo with example. Which helped me to understand things much better.

Re: Help me out to understand the below script with IterNo()

Wed, 26 Mar 2014 22:14:27 GMT

Hi,

Replying late to this post. IterNo gets the number of iterations performed so far.

By this def, it makes sense that it can only be used with some loop.

ex. AUTOGENERATE (1) WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate)

We can use the above logic to generate date ranges of size one day starting

from vMinDate to vMaxDate (variables). Btw, QV stores dates as number of days.

It seems to me that QV stores the number of iterations somewhere and gets
that value from IterNo().

So, the above loop is like this in any programming language -

int count = 0;// like iterNo

int vMinDate = 11; // say 11th day from Jan 1.

int vMaxDate = 15

while( (vMinDate + count) < vMaxDate )

{

starts = vMinDate

ends = vMinDate + 1

print(starts, ends);

count = count + 1;

}

Output -

starts, ends

11,12

12,13,

13,14

14,15

HTH