topic Re: IterNo() vs RowNo() in QlikView

IterNo() vs RowNo()

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

Hi All,

Could you please give some examples on differences between IterNo() vs RowNo().

Actually i have done some testing and found one different with autogenerate you can see in sample file.

I have attached file here.

Thanks,

Nihhal.

Re: IterNo() vs RowNo()

sunny_talwar — Mon, 10 Oct 2016 14:01:11 GMT

AFAIK, IterNo() function only when you use While statement. Whereas RecNo() and RowNo() works with or without While. IterNo() is similar to using a loop find it useful in creating dates between start and end date for example.

Update: Did not see your qvw, but you do have an example of start and end date in there

Re: IterNo() vs RowNo()

swuehl — Mon, 10 Oct 2016 14:59:34 GMT

I think the HELP explains both functions quite well.

(and I believe you know the iterno() help page, your sample code is part of the HELP page)

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/CounterFunctions/IterNo.htm

If you still have problems seeing the fundamental difference, have a look at

Loops in the Script

Counters in the Load

Looking at your your script code comments, it looks like you also want to have a look at the difference between a RESIDENT and a PRECEDING LOAD:

Preceding Load

Re: IterNo() vs RowNo()

nihhalmca — Sun, 05 Mar 2017 07:47:04 GMT

Hi Sunny,

we can use RowNo() with while like IterNo(). Both give same results, try this:

LET vMin=NUM(MakeDate(2010,12,30));

LET vMax=NUM(Today());

CALENDAR: //Using RowNo()

LOAD

Date($(vMin)+RowNo()-1,'DD/MM/YYYY') AS Date

AutoGenerate 1

While $(vMin)+RowNo()<=$(vMax);

CALENDAR: //Using IterNo()

LOAD

Date($(vMin)+IterNo()-1,'DD/MM/YYYY') AS Date

AutoGenerate 1

While $(vMin)+IterNo()-1<=$(vMax);

Note:

Both will give same result.

Combination of RowNo() and IterNo() also will give same result.

Re: IterNo() vs RowNo()

ahaahaaha — Sun, 05 Mar 2017 08:24:26 GMT

Hi Nihhal,

I think Sunny will express you their opinion. Let me say that your code snippet - a particular case of coincidence results. This is due to the equality of sequential read one record, in which the values of RowNo() and IterNo() are the same.

Try to create a table of this type from your table "CALENDAR", for example,

Date

01/01/2017

02/01/2017

03/01/2017

etc.

Each record of table "CALENDAR" should just read twice

CALENDAR3:

LOAD

Date as DateIterNo

Resident CALENDAR

While IterNo() < 3;

And here is how will look the values IterNo() and RowNo() during the loading table

Date IterNo() RowNo()

01/01/2017 1 1

01/01/2017 2 2

02/01/2017 1 3

02/01/2017 2 4

03/01/2017 1 5

03/01/2017 2 6

etc.

So your code works. But this is a particular case. The whole assignment the functions IterNo() and RowNo() different.

Regards,

Andrey

Re: IterNo() vs RowNo()

nihhalmca — Sun, 05 Mar 2017 12:41:10 GMT

Hi Andrey, thanks for your explanation.

Unfortunately i could not understand much, if you do not mind can you tell direct definition to the IterNo() in easy manner please and which cases we use.

Thanks,

Nihhal.

Re: IterNo() vs RowNo()

ahaahaaha — Sun, 05 Mar 2017 14:23:57 GMT

Hi Nihhal.

IterNo() - this is counter the number of times considered the current record in the source table at load. Each current record will be re-read from the source table until the condition WHILE.

Source table, for example

Table1:

ID1, ID2, Volume

1 5 10

2 6 20

3 7 30

I want to get out of Table1 the following table

Table2

ID Volume1

1 10

5 10

2 20

6 20

3 30

7 30

Each record must be read twice. it is easy to do with IterNo()

Table2:

LOAD

If(InerNo() = 1, ID1, ID2) as ID

Volume as Volume1

Resident Table1

While IterNo() <=2;

In short like this

Regards,

Andrey

Re: IterNo() vs RowNo()

Peter_Cammaert — Sun, 05 Mar 2017 17:29:33 GMT

IMHO there is a pretty simple explanation for this that positions three related functions - RecNo(), IterNo() and RowNo() - where they belong.

Imagine an imaginary LOAD statement with a source, a target internal table, and a WHILE suffix. Like this:

TableA:

LOAD columnExpressionsOptionallyUsingAnyOfTheThreeFunctions

FROM SourceA

WHILE WhileExpressionUsingIterNo;

This LOAD statement will execute multiple times, and as long as there are input records. When reading record N (a single row), the following happens:

RecNo() returns N, as this is the row position in the original data source
The LOAD statement loops and executes the column expressions a number of times on the original input data from this single source row as long as the WHILE expression is true. The loop counter is hidden, but IterNo() returns the current loop counter value, starting from IterNo() = 1 ... P. So in essence, 1 input row begets P output rows.
The output of this repeating LOAD is stored in the internal table, let's say starting at position RowNo() = X+1 (X being determined by the previous executions and loops) The last loop result will be stored at position RowNo() = X+P.
Repeat from the first bullet for source row RecNo() = N+1 until the data source is exhausted.

Net result:

Inputrow RecNo() = N -> Processed IterNo()=1...P times -> Resulting rows stored as RowNo() = X+1 ... X+P

Note that depending on your While expression, the P value can vary between 1 and an arbitrary upper limit for each input row.

Best,

Peter

[Edit] Added a picture "because they're worth a thousand words..."

Re: IterNo() vs RowNo()

avinashelite — Mon, 06 Mar 2017 05:45:47 GMT

from help

RecNo( )

Returns an integer for the number of the currently read row of the internal table. The first record is number 1.

RowNo( )

Returns an integer for the position of the current row in the resulting QlikView internal table. In contrast to RecNo( ), which counts the records in the raw data table, the RowNo( ) function does not count records that are excluded by where clauses and is not reset when a raw data table is Concatenation to another. The first row is number 1.

Examples:

Raw data tables:

Tab1.csv
A	B
1	aa
2	cc
3	ee

Tab2.csv
A	B
5	xx
4	yy
6	zz

QVTab:

Load *, RecNo( ), RowNo( ) from Tab1.csv where A<>2;

Load *, RecNo( ), RowNo( ) from Tab2.csv where A<>5;

The resulting QlikView internal table:

QVTab
A	B	RecNo( )	RowNo( )
1	aa	1	1
3	ee	3	2
4	yy	2	3
6	zz	3	4

hope this helps