Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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".
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?
intresting thread !!!
anant
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
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
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.
HIC
Noted. Thanks HIC !!
ANANT
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: