topic Re: Order By does not impact Distinct Load. in QlikView

Order By does not impact Distinct Load.

agpolestar — Mon, 06 Jun 2016 11:03:11 GMT

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.

Re: Order By does not impact Distinct Load.

Clever_Anjos — Mon, 06 Jun 2016 11:37:51 GMT

It´s a known behavior.

There´s one idea about future versions

Re: Order By does not impact Distinct Load.

agpolestar — Mon, 06 Jun 2016 11:48:18 GMT

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

Re: Order By does not impact Distinct Load.

Clever_Anjos — Mon, 06 Jun 2016 12:16:15 GMT

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

Re: Order By does not impact Distinct Load.

swuehl — Mon, 06 Jun 2016 14:03:37 GMT

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$);

NoConcatenate

LOAD xTemp as x

Resident TMP

WHERE NOT EXISTS(x,xTemp)

ORDER BY xTemp;

DROP TABLE TMP;

Re: Order By does not impact Distinct Load.

masismykola — Thu, 25 Jan 2018 15:26:13 GMT

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.