LOAD * INLINE [
LOAD max(YearMonth, 2) as Max2YearMonth
LET vValue = peek('Max2YearMonth');
example.qvw 147.5 K
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.
The specified item was not found.'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 The specified item was not found. is spot on when he says that it is not "part of the contract".
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.
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?
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.
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.
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.
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: