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

[nicolas_martin]

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:

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

[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]

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

[nicolas_martin]

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

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

[swuehl]

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

[rwunderlich]

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]

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]

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]

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 · Accepted solution]

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