Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
agpolestar
New 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:

D:

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.

Tags (2)
5 Replies
Employee
Employee

Re: Order By does not impact Distinct Load.

It´s a known behavior.

There´s one idea about future versions

agpolestar
New Contributor

Re: Order By does not impact Distinct Load.

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

Employee
Employee

Re: Order By does not impact Distinct Load.

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

MVP
MVP

Re: Order By does not impact Distinct Load.

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
New Contributor III

Re: Order By does not impact Distinct Load.

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.

Community Browser