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

[swuehl]

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?

[Anonymous]

intresting thread !!!

anant

[Anonymous]

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

[hic]

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

[hic]

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

[Anonymous]

Noted. Thanks HIC !!

ANANT

[nicolas_martin]

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