RowNo() vs RowNo revisited

[josephinetedesc]

Hi All

Part A

I looked at Help but I cannot find information on the difference between RowNo() and RowNo on load but it seems that RowNo will only load if you have a column called RowNo - however, RowNo comes up automatically when loading the script????

Part B

I have one large flat file which I need to break into smaller files - (in one part there are 3 columns which will need to be made into a crosstable)   A static record number  could work as a key field. Is it best to add a record number in the spreadsheet or in Qlikview.

Part C - I don't want any surprises

I have used the

Load

RowNo(),

and this has worked and enables me to check that all rows/records in the spreadsheet have been loaded.

When I used the RowNo() and the crosstable - I needed to be careful when using (count([RowNo()]) - in order that it counted the same row twice if the data occurred twice in the same row.  (ie not to use count(distinct({RowNo()])

In the example below for example - counting how many type1s there were - I wanted the answer 2 - not 1:

RowNumber     Col1          Col2     Col3

1                         type1     type1     type2

It took a while to check this ... so are there any other surprises I should look out for?

(I will also need eventually to categorise type1, type2 etc - but an automatic synthetic join worked - although I might try to do a lookup table using Qlikview -  I think I saw a thread about that ...)

PartD

Should I in fact use RecNo() for my purposes - using and converting a large flat file with the RecNo  being the primary key.  Substituting one for the other has so far not shown any differences.  It probably makes more logical sense as it is the input file that I wish to have a primary key ID for?

Part E

RowNo(Total) - I have seen reference to this - but it does not work ... is it a function or something else?

Thank you all

Jo

[jonathandienst]

A - I  dont understand your question here. RowNo is a field and RowNo() is a function. The field must have come from your data source or was created by the load process - it is not a built in field.

B - test each one to see which you prefer

C - I think RowNo() and RecNo() are both populated before the cross table is expanded. So they refer to the pre-cross tabled data.

D - RecNo() refers to the input rows, RowNo() refers to the output rows. There will be a difference if you are filtering the data (eg with a Where / Distinct / Join / Keep). They also differ when you using a preceding load - RecNo() is only populated at the lowest level and RowNo() is only populated at the highest level of the preceding load.

E - RowNo(TOTAL) is a table function meaningful in the front-end (counts the row number across all dimensions). You cannot use it in the load script.

[josephinetedesc]

A     RowNo just "appears" when I start to write RowNo() ...  and gives me an error - not sure where itcame from.

B     in terms of maintenance - I would need to add the row number in Excel each time records were added - rather than let Qlik do it - so best to let let Qlik do it (when comfortable that is is working as expected)

C.     I got a "oh no" moment when loading the data - ordinary count worked but then when re-loaded I needed to add distinct to one set of calcs and needed to not use distinct in another set of calcs - those with the crosstable.   From this i have learnt best to CONTROL by using Distinct or not using Distinct ....

D.  so if I want a primary key and this to be static - best to use RecNo()

E. Do you mean at load when Qlikview gives you the number of rows loaded per table?That would be really handy.  I have been going to settings and exporting the summary of table and fields.  So I know and use = 'Last Reloaded: ' & ReloadTime( ) which i put on a text object - how do I do this for rows per table please?

Thank you Jonathan

Jo

[jonathandienst · Accepted solution]

>>RowNo just "appears" when I start to write RowNo() ...  and gives me an error - not sure where itcame from

That's the editor auto-complete. Yow will get an error without the parenthesis because it is an error.

>>so if I want a primary key and this to be static - best to use RecNo()

That depends on your data and your requirements. Either one can do the job.

>>Do you mean at load when Qlikview gives you the number of rows loaded per table

No -that is still the load script. in the script, use NoOfRows('myTable') to get the loaded rows in the table named 'myTable'.

[jonathandienst]

>>how do I do this for rows per table please?

Let vSize = NoOfRows('myTable');

   and then display vSize in a text box

[josephinetedesc]

thank you

Jo