In this post I explore the outputs of RecNo() and RowNo() to demonstrate the difference visually.
These two fields are often used interchangeably, but they provide different output. In summary:
– RecNo() is a record number based on source table(s)
– RowNo() is a record number based on the resulting table
As a result, RowNo will always provide a unique integer per row in an output table, while RecNo is only guaranteed to be unique when a single source is loaded (RecNo is based on each single source table, interpreted individually rather than collectively).
The data set
A simple table of random numbers was used as the input to this script, as I’ve been using this for other recent tests.
The following tests were run to demonstrate the difference in properties between the two functions:
– SourceTable: Straight load of the source
– Test1: Resident load of SourceTable
– Test2: Resident load of values in Test1 where the value ends in a digit less than 5 (e.g. 444, 2, 1033 are all included, but 5, 15, 1029 are excluded)
– Test3: Resident load of all values in Test2
The output is as expected:
RecNo maintains the record number of the parent table, or source. In Test1 and Test2 this matches as the source data has been unchanged each time (the same rows, in the same order). In Test3 it is different, because it is referring to the reduced Test2 table (which is missing rows 1-4 from the DataSource – therefore record 1 in this table is the value 52).
RowNo starts at 1 and increments on each record in line with the record number of that table. As the order of the data isn’t changed, the RowNo ascends with the Value.