Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I only recently realised that the 'order by' clause in a QV load statement was different from that of SQL (QV it is the order of rows presented to the load whereas SQL is the order of the resultant table).
How is a load statement processed in terms of evaluation? For example
load fielda, fieldb from file1;
inner join
load fielda, fieldc as fieldx, lookup(......) as fieldz from file2;
is the lookup perfomed before the join?
Is the order defined somewhere?
Regards,
Gordon
While I haven't tested this specific case, I'm pretty confident that the lookup() would occur before the join. The join is on the RESULT in fieldz as best I understand it. It should be simple enough to test.
As for the order by, I've never thought of QlikView tables as having any order whatsoever, even a random order. QlikView tables, as best I understand it, are not a list of records. They're a highly-compressed data cloud. Lists are merely extracted from the cloud on the fly. As far as I know, all row sorting is done on the fly. I DO believe (with no direct evidence) there are ordered lists of field VALUES somewhere in the system. But not ordered lists of records. I am not privy the the QlikView internals, so I could, of course, be completely wrong.
Edit: Well, I suppose it isn't direct evidence of field lists, as even those could be extracted from a more complicated data structure, but you can actually reference the field lists directly, independently of any table. For example, I'm using this code to retrieve a list of date values without referencing ANY table already loaded:
[Date]:
LOAD fieldvalue('Date',iterno()) as Date
AUTOGENERATE 1
WHILE len(fieldvalue('Date',iterno()))
;
While I haven't tested this specific case, I'm pretty confident that the lookup() would occur before the join. The join is on the RESULT in fieldz as best I understand it. It should be simple enough to test.
As for the order by, I've never thought of QlikView tables as having any order whatsoever, even a random order. QlikView tables, as best I understand it, are not a list of records. They're a highly-compressed data cloud. Lists are merely extracted from the cloud on the fly. As far as I know, all row sorting is done on the fly. I DO believe (with no direct evidence) there are ordered lists of field VALUES somewhere in the system. But not ordered lists of records. I am not privy the the QlikView internals, so I could, of course, be completely wrong.
Edit: Well, I suppose it isn't direct evidence of field lists, as even those could be extracted from a more complicated data structure, but you can actually reference the field lists directly, independently of any table. For example, I'm using this code to retrieve a list of date values without referencing ANY table already loaded:
[Date]:
LOAD fieldvalue('Date',iterno()) as Date
AUTOGENERATE 1
WHILE len(fieldvalue('Date',iterno()))
;
Thanks John.
I think you have hit the issue on the head. Like you, I spend time testing to find the optimal code but apart from the effort, the results are subject to external conditions such as other activity on the server. The conceptual processing sequence of SQL is well documented (and check out 'order by' sequencing for QV in the help text!) - would'nt it be nice if Qliktech could do the same?
Regards,
Gordon