topic Count Row Numbers - Distinct load in QlikView

Count Row Numbers - Distinct load

peschu123 — Thu, 13 Feb 2014 16:36:30 GMT

Hi,

I load some data from a resident table. It's a distinct load, to drop duplicate rows.

I tried to use rowno() or recno() but if I do this, I always get the duplicates in the final table.

Do I really have to load it in a temporary table first to get the count of the rows, or is there a "smarter" way to do it.

Thanks in advance..

Best Regards,

Peter

Re: Count Row Numbers - Distinct load

Thu, 13 Feb 2014 16:40:39 GMT

Maybe a preload might help.Use distinct only in the actual load. Like

Load

Rowno() as Pky ;

Load Distinct

Resident Table;

Edit:

To the row no you can do something like this

Test:

Load Distinct * Resident Table;

Let vRowCount = NoOfRows('Test') ;

The variable vRowCount will have the number of records in table Test.

Thanks

Re: Count Row Numbers - Distinct load

rwunderlich — Thu, 13 Feb 2014 16:40:44 GMT

How about NoOfRows('mytable') ?

-Rob

Re: Count Row Numbers - Distinct load

luis_pimentel — Thu, 13 Feb 2014 16:40:54 GMT

You can load like...

Load Distinct

[Customer ID],

[Customer Name],

....

From FileName;

Re: Count Row Numbers - Distinct load

maxgro — Thu, 13 Feb 2014 16:41:15 GMT

from online help

NoOfRows('TableName ' )

Returns the number of rows (records) in a previously loaded table.

If the function is used within a load statement, it must not reference the table currently being loaded.

Examples:

LET a = NoOfRows('tab1');

Load * from abc.csv where NoOfRows( )<30;

Re: Count Row Numbers - Distinct load

peschu123 — Thu, 13 Feb 2014 16:43:08 GMT

I tried that too. It leads to the same result.

Re: Count Row Numbers - Distinct load

peschu123 — Thu, 13 Feb 2014 16:49:59 GMT

NoOfRows('TableName' ) gives me 0 or 34 but not 1...2..3..4...34

EDIT: OK I think I expressed it wrong, I just want to make an id field with the row number in this table. In my testdate I have one duplicate row. If I use rowno() or recno() during distinct load, I get the duplicate line too.

Re: Count Row Numbers - Distinct load

Thu, 13 Feb 2014 17:07:16 GMT

This is quite interesting. I guess you have to create a temporary table before using RowNo().

Just a small tip. If your table size is large instead of resident load store it in a qvd and read from that. Would be faster.

Re: Count Row Numbers - Distinct load

peschu123 — Thu, 13 Feb 2014 17:14:54 GMT

Yes this is the solution I actually use.

I think its because if you use a row number the rows are not equal anymore

EDIT: One possible "solution" could be autonumber(hash...) over all fields. But this is just a theoretical solution...