topic Re: Why I can't use an Order By clausule in a qvd load? in QlikView

Why I can't use an Order By clausule in a qvd load?

julioarriaga — Wed, 01 Aug 2018 15:47:19 GMT

I know it is not possible, but I just want to know the design reason.

Thanks in advance.

Re: Why I can't use an Order By clausule in a qvd load?

Chanty4u — Wed, 01 Aug 2018 15:52:47 GMT

you can try this way

ssample code looks like this

Temp:

Load *

from C:\QlickView\Dashboard\PATIENTLEVELDATA_4.QVD (qvd);

PATIENTLEVELDATA:

Load *,1 as Flag

Resident Temp Order By RECDATE;

Drop table Temp;

Re: Why I can't use an Order By clausule in a qvd load?

Chanty4u — Wed, 01 Aug 2018 15:59:55 GMT

try this

TempTable:

LOAD [QWT Budget],

F2,

F3,

F4,

F5,

F6,

FROM

(qvd);

Data:

LOAD

RESIDENT TempTable

ORDER BY F2;

exit Script;

Re: Why I can't use an Order By clausule in a qvd load?

Anil_Babu_Samineni — Thu, 09 Aug 2018 00:35:17 GMT

I really don't know the proper answer. But, We can expect something from rwunderlich

Re: Why I can't use an Order By clausule in a qvd load?

vishsaggi — Thu, 09 Aug 2018 00:53:01 GMT

I believe this could be the reason you cannot do an order by with field list on a qvd load.

Ref: https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/QVD_files.htm

A QVD file holds exactly one data table and consists of three parts:

A well formed XML header (in UTF-8 char set) describing the fields in the table, the layout of the subsequent information and some other meta-data.
Symbol tables in a byte stuffed format.
Actual table data in a bit-stuffed format.

So when you load the table from qvd it will do an optimized load if no conditions were given. Then after you load qvd into a table using Resident load as the table is already loaded into memory as a standard table on which you can do an order by to sort.

This is what i believe that happens internally.

Re: Why I can't use an Order By clausule in a qvd load?

marcus_sommer — Thu, 09 Aug 2018 10:54:41 GMT

This behaviour is not directly related to a qvd-load else to each load of external data. The reason is the special data-format in which qlik the data reads and stores which is internally the same as in qvd's - see here the comment from vishsaggi‌.

This means there are not really data on a record-level else the record contained only the pointers to the symbol-tables. I think the load-statement could logically be extended with some intermediate steps (each tool/logic will need at least one intermediate step) to cover this measure - but I believe it would be much slower as loading the data first and order them within a second step (resident load).

For convenience it might be included within the load-parameters and then trigger a hidden load with a temporary table but in my opinion it wouldn't be really useful because it would hide an important part of the qlik mechanics for the user - and this understanding is an essential part to build datamodels properly.

- Marcus