topic Re: Effect autonumber() on qvw size in QlikView

Effect autonumber() on qvw size

simonvm9114 — Tue, 22 Oct 2019 09:07:45 GMT

Hi everyone,

Can anyone tell me if an autonumber function is supposed to affect the size of a qvw file?

I'm trying to optimize the size of a qvw by using the autonumber function on a concatenated key (%Key1 &'|'& %Key2). The field contains nearly 1 million distinct values and removing the field completely saves around 25% of the disc space of the qvw I'm testing with. Although when I replace the field with an autonumbered one instead of removing it, it doesn't affect the size at all.

What is good to know is that for performance reasons, I don't use the autonumber() function directly on the key. Instead I load a mapping table with [%ConcatKey] AS Key, autonumber([%ConcatKey]) AS Value. I apply this mapping on the key in both the metricstable and the dimensions I load.

When I save 2 instances of the distinct values of the key in a QVD, one as is and the other one as autonumber, the autonumbered values take up like half the memory size on my computer. So when storing the data in a qvd file, it does affect the size.

Re: Effect autonumber() on qvw size

marcus_sommer — Tue, 22 Oct 2019 11:54:19 GMT

If you keep the origin key-field (renamed) within the datamodel and adds an autonumber(key) you won't get really a benefit but if you remove the origin key-field the autonumber(key) will have 1M nurmeric values instead of 1M string-values and those numeric values doesn't need to be stored separately because the values = the pointer to the datatable.

If you monitors it by the file-size or with the mem-files you will see that the size shrinked - how much especially in regard to the overall size of your application depends on your data.

- Marcus

Re: Effect autonumber() on qvw size

Brett_Bleess — Wed, 30 Oct 2019 20:44:19 GMT

Just wanted to add one more thing in addition to what Marcus has already explained, it is a Design Blog post that may help solidify things further:

https://community.qlik.com/t5/Qlik-Design-Blog/Autonumber-function/ba-p/1464970

Regards,
Brett