topic Autonumber in Script in QlikView

Autonumber in Script

QFanatic — Wed, 14 Apr 2021 11:36:37 GMT

hi all,

so as I understand I cannot use AutonumberHash if the data gets reloaded in 2 separate reloads. What I've seen from Help files is that I should use Hash128 etc, but that returns fields that are very long...

What do you do guys generally do to get a persistent value across loads?

Thanks

Re: Autonumber in Script

marcus_sommer — Wed, 14 Apr 2021 13:45:15 GMT

What's the aim for it? Why not just keeping the origin values - especially if they should be used within several applications?

- Marcus

Re: Autonumber in Script

QFanatic — Thu, 15 Apr 2021 06:51:10 GMT

Hi Marcus,

The key fields may potentially be made of of huge values - like '155.98.1237.263.178.32'. I though using autonumberhash might make things better if I could give a numeric value, say 9 for my example, instead of Qlik having to evaluate that terrible long value.

Thanks

Re: Autonumber in Script

martinpohl — Thu, 15 Apr 2021 06:54:21 GMT

so you have to load the datas in the same script, create an autonumberfield and store them into qvd. Then you can load the datas into your apps.

Regards

Re: Autonumber in Script

QFanatic — Thu, 15 Apr 2021 07:11:20 GMT

Hi Martin thank you for your reply.

My challenge comes that I create qvds in one load and comaume them in
another where I load a table from the dB as well based on the key values
from the qvds...

Re: Autonumber in Script

marcus_sommer — Thu, 15 Apr 2021 08:02:56 GMT

So the topic is not to use this (autonumber) key across multiple applications else you want to keep the qvd's smaller? How many records and distinct field-values are there? Does all field-values look like your example or are there more differences in length, numeric vs. alpha-numeric or strings, blanks, NULL's? Are any logics included?

- Marcus

Re: Autonumber in Script

QFanatic — Thu, 15 Apr 2021 09:04:31 GMT

Hi Marcus

I want to be able to reduce the size of the QVD's essentially by using Autonumber on those long key fields. Also make linking between tables that both contain these values, quicker.

The field values will always look like the example I gave ('155.98.1237.263.178.32') but will be different for every single key value of the Table - and we're talking billions of rows.

Re: Autonumber in Script

marcus_sommer — Thu, 15 Apr 2021 09:32:53 GMT

Your dataset seems quite large. More important as the number of records is the number of distinct field-values. Means if there are just a few thousands distinct values by billions of rows your intended optimization won't be really significantly. But if there are quite many distinct values it would be different (just keep in mind that you could have max. 2 billions of distinct field-values within a single field).

I think I would in your case investigate if there is any logic within the values included so that you may capable to left the first or last numbers and/or replace/change other parts. If this isn't possible I would split the value into two pure numeric fields. In the target-application you could string-concat both parts within an autonumber() to get a valid key and to keep the RAM as small as possible.

- Marcus

Re: Autonumber in Script

QFanatic — Thu, 15 Apr 2021 09:40:22 GMT

Thank you Marcus, your advice has been great.

Re: Autonumber in Script

QFanatic — Thu, 15 Apr 2021 09:42:23 GMT

just to be sure I understand you correctly, if the number of distinct values where more, the Autonumber would have a significant influence?

thanks

Re: Autonumber in Script

marcus_sommer — Thu, 15 Apr 2021 09:59:19 GMT

By an autonumber the field-value = pointer-value and therefore the field-value don't need to be stored and the pointer is a bit-stuffed numeric value. Compared with a large string-value it's much smaller.

But it depends on the ratio of the number of distinct values against the number of records and also the number of fields within the table how significantly this optimization is. As far as you don't hit any bottleneck within your environment such measures need to be balanced against the efforts. If you save at least 10% of the file-size you may implement such logic and if it's just a few percent you could put it on the todo-list for later - usually never done ...

- Marcus

Re: Autonumber in Script

QFanatic — Thu, 15 Apr 2021 13:15:20 GMT

Thank you.

Re: Autonumber in Script

marcus_sommer — Fri, 16 Apr 2021 06:58:27 GMT

Just one addition. Because on the fact that you don't need the real values of this field else just an autonumber from it as a key you may consider to drop the key also by changing your datamodel, for example by:

mapping/joining the information from the linked dimension-table
concatenating the information if multiple fact-tables are associated

The aim should be to develop the datamodel to get a star-scheme or even one (big) single table because these approaches have performance-benefits within the UI which are usually much more important as advantages in regard to the storages-space or the script run-times. Quite common but especially by large datasets the worst is the use of link-table datamodels.

- Marcus

Re: Autonumber in Script

QFanatic — Fri, 16 Apr 2021 09:23:20 GMT

Great info thank you Marcus