Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Partner
Partner

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
Highlighted

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

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

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

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

Employee
Employee

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

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

Partner
Partner

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

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

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

MVP
MVP

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

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

MVP & Luminary
MVP & Luminary

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

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

Partner
Partner

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

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;

MVP
MVP

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

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;

MVP
MVP

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

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.

Highlighted

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

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