RecNo and AutoGenerate

[andrea90casa]

Hello community,

I was trying to implement a master calendar table and I have found this solution (I'm posting a very simple sample took by excel with just one column with a few dates):

and everything worked fine, but I did not understand two things:

1) I tried to change RecNo() with RowNo(), but in this case the output is a table with one NULL value. I tried to find out a reason reading the Qlik helps page, but i didn't find anythying.

2) I would like to know how "FieldValueCount('Data') works. I already read the Qlik helps page and it says that this formula counts the distinct value in a table (the argument of this function). So why it gave to me an output of 1 single row when the original table has 6 distinct values? I also tried to force the calculation changing the last piece of code with "AutoGenerate 10", but it gave me the same result: a table with 1 row.

I really appreciate if someone would try to explain me the reasons for these behaviours, or if he/she can gave me some references that i should read in order to understand those.

Thanks in advance,

Andrea

[andrea90casa]

Sorry but it didnt post the Attachments:

Table:

Data

01/02/2019

03/01/2019

02/10/2019

10/10/2019

24/10/2019

26/06/2019

01/02/2019

Script Code:

LOAD
    Data
FROM [lib://Esempi/FieldValue().xlsx]
(ooxml, embedded labels, table is Dati);

Sample:

LOAD
 Num(Min(FieldValue('Data',1))) as Data1
AutoGenerate 10;

[andrea90casa]

The original script code:

LOAD
    Data
FROM [lib://Esempi/FieldValue().xlsx]
(ooxml, embedded labels, table is Dati);

Sample:

LOAD
 Num(Min(FieldValue('Data',RecNo()))) as Data1
AutoGenerate FieldValueCount('Data');

[jonathandienst]

1) In a preceding load, RecNo() references the source and must be in the bottom most element of the preceding load. RowNo() references the output row and must be in the top most element of the preceding load. If you place either of these functions differently, then there is no source (for RecNo()) or no output (for RowNo()) so they do not increment.

2) Your problem is not due to FieldValueCount() and you have answered your own question

[andrea90casa]

Thank you for your answer Jon,

I tried to check with this script code:

Sample:

LOAD
 Num(Min(FieldValue('Data',RecNo()))) as Data1,
    RecNo() as Rec,
    RowNo() as Rec2
Resident Data;

I used Resident instead AutoGenerate and the result was the same, so I prefer this solution at the moment, because it's more familiar to me and I don't well understand how AutoGenerate works yet.

Talking about RecNo() and Row(), I added those two fields at the script in order to see the output. For the first one the result is 7 and if I well understood it's because in a resident load RecNo() looks for the original source table and in that one there were 7 rows. With RowNo() instead the result is 1, because it looks at the internal table, created by Qlik at the end of the script, and in this case there was just 1 row in that table, is it correct?

But I'm not still sure about the behaviour of this formula: FieldValue('Data',RecNo())

I thought that was equal to "INDEX" function in excel and so it gives the n value inside a field, where n is specified by the second argument. But when I tried to remove the "min()" function from the script I got the following output:

Data 1     Rec     Rec1

43497      1          1

43468      2          2

43740      3          3

43748      4          4

43579      5          5

43642      6          6

-                 7          7

So the output of this formula is a set of distinct field's value, isn't it?

Thanks in advance 

Andrea

[jonathandienst]

FieldValue('field', n) returns the nth value of the field from the symbol table. The symbol table is a list of the distinct values of the fields (OK its a bit more than that, but that is what is relevant here.

This can result in very good performance for something like this when the fact table is very large. So this may need to loop over a few dozen to a few hundred entries:

MAXVAL:
Load Max(FieldValue('datefield', RowNo())) as MaxDate
AutoGenerate FieldValueCount('datefield');

compared to the conventional approach, which may need to loop over millions or tens of millions of rows:

MAXVAL:
Load Max(datefield) as MaxDate
Resident FactTable;

[jonathandienst · Accepted solution]

RecNo() and RowNo() will return the same values when there is no preceding load, and no where clause in the load.

[jonathandienst]

AutoGenerate causes the load to execute as many times as specified in the statement:

LOAD RowNo() as Count
Autogenerate 100;

This will create field Count, containing the values from 1 to 100.

LOAD 0 as TransID
Autogenerate 0;

This will create an empty table with the field TransID, but no rows. This is useful as a concatenation target.

[andrea90casa]

Hello Jon,

Thank you very much for your explanation. It was very useful.

Kind Regards,

Andrea