Order By does not impact Distinct Load.

[agpolestar]

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:

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.

[Clever_Anjos]

It´s a known behavior.

There´s one idea about future versions

[agpolestar]

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]

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

[swuehl]

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]

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_name

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