Strange behavior with RecNo()

[#1]

Hello,

I noticed a strange behavior when I create IDs with "RecNo()" while loading.

Lets say that I have 2 files I want data from. In my example, it will be replaced by "autogenerate".

To be sure to have distinct IDs, I decided to use a concatenation between the year, a separator ":" and "RecNo().

With this code

Data:

LOAD '2011:' & RecNo() AS ID,

2011 AS Year,

floor(rand() * 1000) AS Value

AutoGenerate 100;

CONCATENATE (Data)

LOAD '2012:' & RecNo() AS ID,

2012 AS Year,

floor(rand() * 1000) AS Value

AutoGenerate 250;

my result is very strange: my ID are not linked to the right Year!

Some ID are linked to 2 Years (ths shouldn't be possible).

Worse, if I select "2012" in a listbox, I still have "2011" items.

If I replace my ":" separator with "-" (for example), it works perfectly.

Data:

LOAD '2011-' & RecNo() AS ID,

2011 AS Year,

floor(rand() * 1000) AS Value

AutoGenerate 100;

CONCATENATE (Data)

LOAD '2012-' & RecNo() AS ID,

2012 AS Year,

floor(rand() * 1000) AS Value

AutoGenerate 250;

If I replace "RecNo()" by "RowNo()", it works as well...

Data:

LOAD '2011:' & RowNo() AS ID,

2011 AS Year,

floor(rand() * 1000) AS Value

AutoGenerate 100;

CONCATENATE (Data)

LOAD '2012:' & RowNo() AS ID,

2012 AS Year,

floor(rand() * 1000) AS Value

AutoGenerate 250;

I should be tired, because I don't see what I am missing...

ps : I'm using QV 10 SR5.

[rwunderlich · Accepted solution]

That's a good one! Qlikview is trying to be a helpful and eager assistant by interpreting your "yyyy:nn" values as a "hhhh:mm" time value. Therefore 2011:61 = 2012:1. The internal numeric values, which is what is used for association, will be the same.

Recno() is the input record counter so you get overlapping values. Rowno() is the output record counter so you don't have overlapping values. But you would encounter the same problem of overlapping internal values if you loaded 2012 first, so not a good solution.

If you want to use the colon, wrap it in a text() function like this:

text('2011:' & RecNo()) AS ID

-Rob

http://robwunderlich.com

[#3]

That's a good one! Qlikview is trying to be a helpful and eager assistant by interpreting your "yyyy:nn" values as a "hhhh:mm" time value. Therefore 2011:61 = 2012:1. The internal numeric values, which is what is used for association, will be the same.

Hu hu!

I noticed that there was something between the 60th and the 61th, but I didn't made the connection with a time interpretation.

If you want to use the colon, wrap it in a text() function like this:

text('2011:' & RecNo()) AS ID

I put a footnote on my brain : "be sure to use text values in your prefixes".

I changed to

'Y2011:' & RecNo() AS ID

(don't worry, as soon as my tests are done, I surround this by a "autogenerate()" function)