Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

RecNo or 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

To see an example, check out my technical brief that includes the QVW and detailed descriptions of these functions.  In the technical brief, I also discuss the IterNo() function and the FieldValueCount() functions.

Jennell

13 Comments
MVP
MVP

It's also interesting to see how these functions differ when used in preceding loads and resident loads.

Thanks for the clear, concise description here Jennell.

102 Views

Thanks for clarification.

Regards

ASHFAQ

0 Likes
102 Views
mrooney
New Contributor III

Very clear, and useful. Thanks.

0 Likes
102 Views
Not applicable

Thank you for the detailed clarification!

0 Likes
102 Views
Not applicable

Highly informative. Thank you!

0 Likes
102 Views

Recently I was using RecNo() and RowNo() for diagnosing a script with issues.  Both functions proved very useful and I love them as they helped solve the problem.

But I noticed that whilst Recno() appears to run multi threaded, RowNo() appears to run single threaded with the performance hit one would expect from single threading.  This could well be how it has to be as otherwise how would multiple threads keep track of the other threads output row positions.

Can anybody confirm / refute this ?

Here is the simple script I have been using to check it out, commenting the RecNo() and RowNo() in / out to see the differences.

Load

//RecNo() ,

RowNo()) ,

date(today()) ,

'QWERTY' as A

autogenerate 9876543;

102 Views
Not applicable

Nice post Jennell.

0 Likes
102 Views
benrig44
New Contributor III

yes Bill I saw the same thing. Other functions such as Previous have the same issue. I plan to investigate all functions and post a summary when time permits.

THe the other performance killer in Load Scripts which blew my mind was that a Load (or variations) statements WHERE clause did not get processed until *after* any sorts required by an ORDER BY or a GROUP BY clause. So even if your WHERE clause was something extreme like WHERE 1=0 a sort would still take place even though you know (and eventually QVW knows) no rows will be processed.

0 Likes
102 Views
Not applicable

It's really good.

0 Likes
102 Views
sakamsureshbabu
Contributor

very good post

0 Likes
102 Views
Not applicable

Nice post!

0 Likes
102 Views
ankitbisht01
Contributor

Thank You for clarification

0 Likes
102 Views
psankepalli
Contributor III

its excellent post!!

0 Likes
102 Views