Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I am using PEEK on a table with 10 million rows , and its giving me wrong data
??
That means, your field is a mixed value field. It contains not only numeric values but also alphanumeric values.
And thats why you get 3 different results:
Max(DiagnosisObjectID) ==> Maximum of the numeric values
Max(Fieldvalue('DiagnosisObjectID',RecNo())) ==> it's the same as max(DiagnosisObjectID), but I think you will see here a "?" as result, because in this case QlikView don't know how to interpret it (numeric or alphanumeric).
ORDER BY DiagnosisObjectID DESC ==> Maximum of ASCII values
and with MaxString(DiagnosisObjectID) you will get another differnt result.
Normally Max(DiagnosisObjectID) should be the fastest solution of all, but in the case of mixed values QlikView has at first to filter out the numeric values, before it can determine the max value of them and thats why it takes a few of minutes.
Maybe most of your values are filled with blanks on the right or left side.
Could you please provide more information:
-Version (Client/ Server) and OS you are using
- exact script code you are using
Best by uploading a sample QVW that demonstrates your issue.
QlikView 11.20.12904.0 SR12 64 bit
LOAD * FROM $(vPathQVD)Diagnosis.qvd (qvd);
LastDiagnosis:
LOAD DiagnosisObjectID Resident Diagnosis ORDER BY DiagnosisObjectID DESC;
Let vLastDiagnosisObjectID = Peek('DiagnosisObjectID', 0, 'LastDiagnosis')
DROP Table LastDiagnosis;
And what do you get as value in the variable compared to what do you expect to see?
Could you also explain how you confirmed your expectation using a different approach?
Instead of giving me a value, it gave a field name of another column in that table.
I got a correct value using Max(), but it took a few minutes
What other field name? There is only a single field in LastDiagnosis table, right?
If you want to find the maximum value of a field fast, you can also scan the symbols only:
LOAD
Min(Fieldvalue('DiagnosisObjectID',RecNo())) as MinID,
Max(Fieldvalue('DiagnosisObjectID',RecNo())) as MaxID
AUTOGENERATE FieldValueCount('DiagnosisObjectID');
The sort order of the field DiagnosisObjectID is determine by the "Load-From-Qvd" statement. The "Load-Resident-Order By" statement doesn't mean that the field will be stored in a sort order in the memory. The "order by" applies only during the load statement. To get a sort order field, you have to load it resident with a new name.
Diagnosis:
LOAD * FROM $(vPathQVD)Diagnosis.qvd (qvd);
LastDiagnosis:
First 1
LOAD
DiagnosisObjectID as LastDiagnosisObjectID
Resident Diagnosis ORDER BY DiagnosisObjectID DESC;
Let vLastDiagnosisObjectID = Peek('LastDiagnosisObjectID', 0, 'LastDiagnosis');
DROP Table LastDiagnosis;
Both Stephan and Christian have interesting solutions. Thank you both.
I am in the middle of a large load, so I will check both options and let you know.
Thanks, J
Nope, both approaches did not work.
The only thing that works is:
LOAD Max(DiagnosisObjectID) AS MaxDiagnosisObjectID Resident Diagnosis;
Could you upload some sample data?