Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
rp132456
Contributor II
Contributor II

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;

rp132456
Contributor II
Contributor II
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.