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: 
agpolestar
Partner - Contributor
Partner - Contributor

Order By does not impact Distinct Load.

Hi,

PFA the test data (a single column) that is not getting sorted when we write the following code:

😧

LOAD x

FROM

[..\..\..\..\..\..\Downloads\TestData.xls]

(biff, embedded labels, table is Sheet1$);

NoConcatenate

load distinct * Resident D

Order by x;

drop Table D;

Output:

Inline image 1

I know that Order By executes before Load. Thus accordingly, Load Distinct should be applied on the sorted column x which should in turn return the sorted Distinct values.

It would be really helpful if you could help me tell why it is not getting sorted.

5 Replies
Clever_Anjos
Employee
Employee

It´s a known behavior.

There´s one idea about future versions

agpolestar
Partner - Contributor
Partner - Contributor
Author

Thanks Clever Anjos. However, I am intrigued about the code library that runs for Distinct Clause. Can we get the code for it anywhere ?

Clever_Anjos
Employee
Employee

I don´t think it´s available since is proprietary code from Qlik

swuehl
MVP
MVP

As a workaround to keep the sort order, you can use something like

TMP:

LOAD x as xTemp

FROM

[TestData.xls]

(biff, embedded labels, table is Sheet1$);

x:

NoConcatenate

LOAD xTemp as x

Resident TMP

WHERE NOT EXISTS(x,xTemp)

ORDER BY xTemp;

DROP TABLE TMP;

masismykola
Partner - Contributor III
Partner - Contributor III

Hi Stefan,

can you please explain you solution? What I find confusing is that the 1st parameter of exists is x, even though it was renamed in TMP to xTemp.

Exists(field_name [, expr])

field_nameA name or a string expression evaluating to a field name to be searched for. The field must exist in the data loaded so far by the script.

Thank you in advance,

Mykola.