Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

MVP
MVP

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

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?

anantmaxx
Valued Contributor

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

intresting thread !!!

anant

anantmaxx
Valued Contributor

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

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

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

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

anantmaxx
Valued Contributor

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

Noted. Thanks HIC !!

ANANT

Partner
Partner

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

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