Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

RowNo() does not work with GROUP BY or DISTINCT

I'm generating a new table with unique records along with a record identifier, and I'm puzzled as to why I can't combine RowNo() with either DISTINCT or GROUP BY. Autonumber() on the other hand seems to work with GROUP BY.

Any ideas why that is?  The entire script included below.


Thanks!

QUALIFY RowId;

Table:

LOAD * INLINE [

    F1

    10

    10

    11

    12

    12

];

// This works (but missing the ID column)

TableUnique:

NoConcatenate

LOAD DISTINCT *

RESIDENT Table;

// Not working (shouldn't this work?) - includes all (duplicate) rows

TableUniqueWithId:

NoConcatenate

LOAD DISTINCT RowNo() as RowId, *

RESIDENT Table;

// Not working (shouldn't this work?) - RowID is zero for all records

TableUniqueWithIdAlt:

NoConcatenate

LOAD max(RowNo()) as RowId, *

RESIDENT Table

GROUP BY F1;

// Only this works.

TableUniqueWithIdAlt2:

NoConcatenate

LOAD DISTINCT autonumber(F1) as RowId, *

RESIDENT Table;

1 Solution

Accepted Solutions
sunny_talwar

This sort of works also... again not straight forward, but the problem is that RowNo() creates a non-distinct value for each row before the Distinct can filter out duplicates.

QUALIFY RowId;

Table:

LOAD * INLINE [

    F1

    10

    10

    11

    12

    12

];

TableUniqueWithId:

NoConcatenate

LOAD DISTINCT *

RESIDENT Table;

FinalTable:

NoConcatenate

LOAD RecNo() as RowId, *

Resident TableUniqueWithId;

DROP Table TableUniqueWithId;

View solution in original post

2 Replies
sunny_talwar

This sort of works also... again not straight forward, but the problem is that RowNo() creates a non-distinct value for each row before the Distinct can filter out duplicates.

QUALIFY RowId;

Table:

LOAD * INLINE [

    F1

    10

    10

    11

    12

    12

];

TableUniqueWithId:

NoConcatenate

LOAD DISTINCT *

RESIDENT Table;

FinalTable:

NoConcatenate

LOAD RecNo() as RowId, *

Resident TableUniqueWithId;

DROP Table TableUniqueWithId;

Anonymous
Not applicable
Author

You're right - the same seems to apply to SQL as well (for MSSQL at least.) Simply the order in which the aggregates/distinct and RowNo() functions are being evaluated.

Thanks.