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

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

nicolas_martin — Tue, 07 Jan 2014 16:29:58 GMT

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

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

Nicole-Smith — Tue, 07 Jan 2014 16:35:31 GMT

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

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

Clever_Anjos — Tue, 07 Jan 2014 16:37:42 GMT

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

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

nicolas_martin — Tue, 07 Jan 2014 16:41:33 GMT

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

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

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

swuehl — Tue, 07 Jan 2014 17:04:51 GMT

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

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

rwunderlich — Tue, 07 Jan 2014 17:07:12 GMT

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

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

nicolas_martin — Tue, 07 Jan 2014 17:15:55 GMT

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;

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

swuehl — Tue, 07 Jan 2014 17:28:41 GMT

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;

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

swuehl — Tue, 07 Jan 2014 17:48:03 GMT

Rob,

I think you're right, since even

Data:

LOAD DISTINCT * INLINE [

YearMonth

201306

201307

201308

201309

201310

201311

201312

];

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

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

hic — Thu, 09 Jan 2014 12:54:47 GMT

Rob Wunderlich's answer is closest.

The ORDER BY is done before the table is loaded; on the input data. (Not the same as in SQL.)
The DISTINCT is applied after the table is loaded; on the output data.
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

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

swuehl — Thu, 09 Jan 2014 13:13:28 GMT

Henric,

I am just wondering, while QV has a quite efficient implementation for where not exists, why this should potentially not be used also internally for the LOAD DISTINCT mechanism (of course you would need to build something like a composite key of all field values in a record to get distinct records).

I know that the where clause works on the input records, but I believe potentially, the same exists() mechanism could work also on the output table.

This should / could preserve input order (as I think it does in my above sample).

Not sure if I made myself clear or I am missing something here, it's not my day today.

Regards,

Stefan

P.S: And now to something (not) completely different:

The Help says: "distinct is a predicate used if only the first of duplicate records should be loaded."

In the context of our contract, what does first of mean here?

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

Anonymous — Thu, 09 Jan 2014 13:14:01 GMT

intresting thread !!!

anant

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

Anonymous — Thu, 09 Jan 2014 13:44:53 GMT

true i have checked it HIC

That means in you can not load Distinct items straight with order by asc/desc

first kiss all records and then kiss good bye either swehul where not exist or some other work around??

but any any it will burden a qvw once with duplicate records..is i m missing something or something is left to be understood by me.

anant

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

hic — Thu, 09 Jan 2014 13:48:27 GMT

Stefan

Yes, a work-around using where not Exists() is certainly feasible. And it would preserve the order. But it might be slower than DISTINCT.

Concerning "first of duplicate records". I am probably responsible for those words in the documentation many years ago... The algorithm for DISTINCT involves re-sorting the values and removing duplicates, and whether it really is the first value that is kept or not, I don't know. And it is really not relevant since the values are identical.

HIC

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

hic — Thu, 09 Jan 2014 14:12:49 GMT

You right that you cannot have DISTINCT and ORDER BY in the same Load statement and expect a sorted table.

But, there are many simple work-arounds.

Use DISTINCT in a first Load statement and the ORDER BY in a second - and the final table will be sorted. Note that if Nicolas had used DISTINCT in the inline Load instead, there would be no problem.
Use DISTINCT and ORDER BY in the same Load - and the Symbol table will be sorted (used for "Sort by Load order")
Use Where Not Exists() instead of DISTINCT.

HIC

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

Anonymous — Fri, 10 Jan 2014 05:42:10 GMT

Noted. Thanks HIC !!

ANANT

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

nicolas_martin — Tue, 21 Jan 2014 08:50:09 GMT

QlikView support says that's not a bug (it's a feature ).

In order to be "modified" (not "corrected"), I had to create an idea and wait until there is enought vote so that the developpers think about it:

http://community.qlik.com/ideas/3272