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: 
nicolas_martin
Partner - Creator II
Partner - Creator II

My "ORDER BY" on a resident table doesn't work

Hello,

I need to get the value before the last in a specific field, in QlikView script.

Here is the code I use:

Data:

LOAD * INLINE [

YearMonth

201312

201312

201306

201307

201308

201309

201310

201311

201311

];

OrderBy:

LOAD

DISTINCT YearMonth AS TMP_date

RESIDENT Data

ORDER BY YearMonth DESC;

LET vValue = peek('TMP_date', 1, 'OrderBy'); // 1 = peek the 2nd value.

This script returns:

2014-01-07_165650.png

and my variable is "201306", whereas it should be "201311".

--> the ORDER BY don't make sense.

Could you explain me why?

I use the following workaround:

OrderBy:

LOAD

DISTINCT YearMonth AS TMP_date

RESIDENT Data

ORDER BY YearMonth ASC;

OrderBy2:

LOAD

DISTINCT TMP_date AS TMP_date2

RESIDENT OrderBy

ORDER BY TMP_date DESC;

LET vValue = peek('TMP_date2', 1, 'OrderBy2');

It works, but it's not "clean".

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

Rob Wunderlich's answer is closest.

  1. The ORDER BY is done before the table is loaded; on the input data. (Not the same as in SQL.)
  2. The DISTINCT is applied after the table is loaded; on the output data.
  3. The DISTINCT can potentially affect the order of the records. (And does in most cases.)

Hence, a Load statement with both ORDER BY and DISTINCT may still come out un-ordered.

I agree that this may seem counter-intuitive, but it is difficult to change. And further, I am not sure it is theoretically possible: Sorting after the DISTINCT is not possible since the DISTINCT is applied not only on the current table, but all tables that are concatenated with the current one - whereas ORDER BY only affects the current table. Further, the DISTINCT is based on the output fields, whereas the ORDER BY is based on the input fields - and the two sets need not have any fields in common...

No, I would say that Rob Wunderlich is spot on when he says that it is not "part of the contract".

HIC

View solution in original post

16 Replies
Nicole-Smith

What about:

Data:

LOAD * INLINE [

YearMonth

201312

201312

201306

201307

201308

201309

201310

201311

201311

];

Max:

LOAD max(YearMonth, 2) as Max2YearMonth

RESIDENT Data;

LET vValue = peek('Max2YearMonth');

Clever_Anjos
Employee
Employee

Very weird, because without DISTINCT clause your script loads sorted right.

nicolas_martin
Partner - Creator II
Partner - Creator II
Author

I'm not looking for a workaround, I'm looking for an explanation.

Thank you anyway, your workaround is more elegant than mine!

swuehl
MVP
MVP

Since an ascending sort works, I would consider this as a bug in the software.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Here's my shot at an explanation.

While the Help states:

  "distinct is a predicate used if only the first of duplicate records should be loaded...",

I think in practice

DISTINCT is performed against the output table at the end of the load. It apparently does not preserve the input order.

The help for Order By states:

  "order by is a clause used for sorting the records of a resident table before they are processed by the load statement."

Order By sets the order of the input table, it says nothing about controlling the order of the output table. It's just coincidental that if you write out without distinct (or potentially other operators), you get the same order by in the output. But I don't think it's part of the contract.

-Rob

nicolas_martin
Partner - Creator II
Partner - Creator II
Author

ASC doesn't work either.

With this data, it works, but add a simple line above, it doesn't:

Data:

LOAD * INLINE [

YearMonth

201306

201312

201312

201306

201307

201308

201309

201310

201311

201311

];

OrderBy:

LOAD

DISTINCT YearMonth AS TMP_date

RESIDENT Data

ORDER BY YearMonth ASC;

swuehl
MVP
MVP

You are right.

Since you looking for explanations, I think my explanations would still be: It's a defect in the software.

Just for fun, here is another workaround:

OrderBy:

LOAD

YearMonth AS TMP_date

RESIDENT Data WHERE NOT EXISTS(TMP_date,YearMonth)

ORDER BY YearMonth DESC;

swuehl
MVP
MVP

Rob,

I think you're right, since even

Data:

LOAD DISTINCT * INLINE [

YearMonth

201306

201306

201307

201308

201309

201310

201311

201311

201312

201312

];

corrupts the order. Would be interesting to know why, though.

hic
Former Employee
Former Employee

Rob Wunderlich's answer is closest.

  1. The ORDER BY is done before the table is loaded; on the input data. (Not the same as in SQL.)
  2. The DISTINCT is applied after the table is loaded; on the output data.
  3. The DISTINCT can potentially affect the order of the records. (And does in most cases.)

Hence, a Load statement with both ORDER BY and DISTINCT may still come out un-ordered.

I agree that this may seem counter-intuitive, but it is difficult to change. And further, I am not sure it is theoretically possible: Sorting after the DISTINCT is not possible since the DISTINCT is applied not only on the current table, but all tables that are concatenated with the current one - whereas ORDER BY only affects the current table. Further, the DISTINCT is based on the output fields, whereas the ORDER BY is based on the input fields - and the two sets need not have any fields in common...

No, I would say that Rob Wunderlich is spot on when he says that it is not "part of the contract".

HIC