Count Row Numbers - Distinct load

[peschu123]

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

[#2]

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

AJ

[rwunderlich]

How about NoOfRows('mytable') ?

-Rob

[luis_pimentel]

You can load like...

Load Distinct

     [Customer ID],

     [Customer Name],

     ....

From FileName;

[maxgro]

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;

[peschu123]

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

[peschu123]

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.

[#8]

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.

[peschu123]

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...