topic Re: RowNo() does not work with GROUP BY or DISTINCT in QlikView

RowNo() does not work with GROUP BY or DISTINCT

Anonymous — Wed, 06 Dec 2017 19:46:54 GMT

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 [

];

// 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;

Re: RowNo() does not work with GROUP BY or DISTINCT

sunny_talwar — Wed, 06 Dec 2017 20:18:14 GMT

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 [

];

TableUniqueWithId:

NoConcatenate

LOAD DISTINCT *

RESIDENT Table;

FinalTable:

NoConcatenate

LOAD RecNo() as RowId, *

Resident TableUniqueWithId;

DROP Table TableUniqueWithId;

Re: RowNo() does not work with GROUP BY or DISTINCT

Anonymous — Thu, 07 Dec 2017 14:02:01 GMT

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.