topic Is it usefull to format data in a Load script for a QVD? in QlikView

Is it usefull to format data in a Load script for a QVD?

Anonymous — Wed, 03 Aug 2016 13:00:37 GMT

Is it useful to format data in a Load script for a QVD:

Example:

Sales:

Load

date(mydate) as mydate

money(mysale) as mysale;

SQL

select mydate, mysale from shop;

store Sales into Sale.qvd (qvd);

drop table Sales;

Will the Report which loads the QVD benefit from the formating?

Re: Is it usefull to format data in a Load script for a QVD?

Wed, 03 Aug 2016 16:12:46 GMT

Hello Markus,

Load from QVD file will always be faster that from DB. It is also good to format data before storing it into QVD. Why? Because then in other apps you can utilise the benefits of optimised QVD load (which is up to 100 times faster than non optimised one)

BR,

Kuba

Re: Is it usefull to format data in a Load script for a QVD?

Anonymous — Thu, 04 Aug 2016 06:48:55 GMT

Yes i know it's loads faster from QVD.

But from my test so far, it ignores the format money() and date() completely.

Re: Is it usefull to format data in a Load script for a QVD?

marcus_sommer — Thu, 04 Aug 2016 09:53:32 GMT

It's a very good question which point is the best place to format the data? Then to format data, especially date- and time-fields, meant to create dual-values which need more bytes to be stored then pure numeric values.

The differences between them could be watched within the attachments.

But this is too much simplified to generate from them a general rule. Then in my example there are (intentionally) only distinct values which are not representative for a normal transactional fact-table and applied within a dimension-table (with a significantly higher degree on distinct values - the date will probably be but a year won't) it musn't be very important compared with the size of the fact-table.

Further considerations could be the layer on which a formatting will be performed - then each formatting will break an optimized load. If you need to apply anyway transformings by loading from a qvd the formattings won't be make much difference but if not it will make a great difference.

Therefore it will be always depend on the concrete scenario which layer will be more suitable. In your case I would tend to try all performings on one point - either within the preceeding load (to load afterwards optimized) or within a following transforming-step.

A further possibility is to perform the formatting in the last possible layer - within the gui (in document properties tab numbers). I have this applied within my attachment and you could see that the file-size of the qvw's are the same - this meant you saved some bytes by the file-size but you will have some more processor overhead to perform the formatting on the fly whereby I think the needed RAM for it will be the same because very probably will qlik cache these action.

But nevertheless it would be very interesting to see if gui-formatting within a bigger real-case have any advantages - maybe someone had tested it?

- Marcus