Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

sspawar88
Contributor II

Autonumber(), Recno(), rowno()

Can anyone please tell me what is the use of these function with short Exmaple ?

1 Solution

Accepted Solutions
sujeetsingh
Honored Contributor III

Re: Autonumber(), Recno(), rowno()

Do you know the difference between the RecNo() and the RowNo() functions?  You may think that you can use these interchangeably to count the records or rows in a table but these functions differ slightly.  The RecNo() function will return an integer for the record currently being read from the source table starting with number one.  The RowNo() function also returns an integer but it represents the position of the row in the resulting QlikView internal table.  So while RecNo() counts from the source table, RowNo() counts from the resulting table.

In the script below the fields from the Temp table are loaded along with fields to represent the RecNo and the RowNo.  A Where clause is used to load all records where Number does not equal 3.

script.png

The resulting table below shows the results of this script.  In the RecNo field we see values 1 through 10 excluding 3.  This is because the script excluded records where Number = 3 so while that record was read, it was excluded from the results.  In the RowNo field we see values 1 through 9 for the position of each row in the results table.  The fact that some rows were excluded does not affect the RowNo field because we are only looking at the internal table that is created.  The RecNo field indicated the order in which the records were read from the source table and the RowNo field indicates the positon of the record in the internal table.

table.png

9 Replies
Highlighted
gautik92
Valued Contributor III

Re: Autonumber(), Recno(), rowno()

RecNo() is the record number of the input to the Load statement.

RowNo() is the record number of the output of the Load statement.

See more on Counters in the Load

for autonumber have a look at this

QlikView Addict: QlikView Functions: autonumber()

sspawar88
Contributor II

Re: Autonumber(), Recno(), rowno()

what it mean for

1)input to the Load statement.

2)output of the Load statement.

Can you give me the small example please ?

Partner
Partner

Re: Autonumber(), Recno(), rowno()

If you have a load like this:

Table:
Load ID, Date inline [

ID, Date

1, 1

2, 3

3, 2

4, 1

];

Final_table:

load ID, Date, RowNo, RecNo() as RecNo, RowNo() as RowNo resident Table order by Date asc, ID, asc;

Drop table Table;

The resulting table will look like this:

ID, Date, RowNo, RecNo

1, 1, 1, 1

4, 1, 2, 4

3, 2, 3, 3

2, 3, 4, 2

The RecNo indicates the original order of the records and RowNo now indicates the new order.

sujeetsingh
Honored Contributor III

Re: Autonumber(), Recno(), rowno()

Do you know the difference between the RecNo() and the RowNo() functions?  You may think that you can use these interchangeably to count the records or rows in a table but these functions differ slightly.  The RecNo() function will return an integer for the record currently being read from the source table starting with number one.  The RowNo() function also returns an integer but it represents the position of the row in the resulting QlikView internal table.  So while RecNo() counts from the source table, RowNo() counts from the resulting table.

In the script below the fields from the Temp table are loaded along with fields to represent the RecNo and the RowNo.  A Where clause is used to load all records where Number does not equal 3.

script.png

The resulting table below shows the results of this script.  In the RecNo field we see values 1 through 10 excluding 3.  This is because the script excluded records where Number = 3 so while that record was read, it was excluded from the results.  In the RowNo field we see values 1 through 9 for the position of each row in the results table.  The fact that some rows were excluded does not affect the RowNo field because we are only looking at the internal table that is created.  The RecNo field indicated the order in which the records were read from the source table and the RowNo field indicates the positon of the record in the internal table.

table.png

sujeetsingh
Honored Contributor III

Re: Autonumber(), Recno(), rowno()

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 toRecNo( ), 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

tripatirao
Contributor II

Re: Autonumber(), Recno(), rowno()

Hi

Go through attached .qvw file .you can easily understand recno() and rowno().

krishna20
Valued Contributor II

Re: Autonumber(), Recno(), rowno()

Hi,

Check once the below link.

RecNo or RowNo?

sspawar88
Contributor II

Re: Autonumber(), Recno(), rowno()

HI,

can you please explain this.?

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

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

why you put <> (Not equal to) condition here.

means is it necessary ?

because i also seen such condition in below link.

RecNo or RowNo?

qlikviewnovice
Valued Contributor II

Re: Autonumber(), Recno(), rowno()

Hi Satish,

Have a look:

rowno() and recno()

Re: auto number

Auto Number

Hope it helps you to understand well.