Autonumber() - with a linktable?

[datanibbler]

Hi,

I would like to replace some of the keys in my datamodel, which are usually made up of a combination of both text and numeric fields, by using the Autonumber() function or its variations because QlikView can deal with numbers better and it should positively affect the performance.

<=> The issue is, we have a Linktable in our datamodel, several tables are linked to each other and the LinkTable via a keyfield - well, not to each other actually because the key is necessarily different, it is built differently, just named the same.

The links "onward" from the LinkTable are again different, so that would not be an issue if I were to use some Autonumber() function.

But how can I avoid the risk of the keys from several tables matching, thus linking those tables between each other AND to the LinkTable, thus creating a synthetic key?

Thanks a lot!

Best regards,

DataNibbler

[swuehl]

Not quite sure I understand your issue. As I understand, you want to change the key values, not the key field names.

So why should this change affect the risk to create synthetic keys?

Besides this, you can create different Autonumber() bins using a parameter to Autonumber():

Autonumber( Field1 & '-' & Field2, 'Bin1') as Key1,

Autonumber( Field3 & '-' & Field4, 'Bin2') as Key2,

[datanibbler]

Ah, yes, the second parameter - I had read about it, but forgotten for now, I will read up on this again - using this I could make sure that the no key from table A can match a key from table B even if they use the same AutoNumber() function, no?

Thanks a lot!

P.S.: Would you say that the AutoNumber() fct with an ID would in that respect be safer to use than the AutoNumberHash128? If I understand correctly, the latter one cannot have an Auto-ID and its advantage is mainly that it is independent of source data sorting - which should not change since the source data for the "datamodel.qvw" comes from some "transformation.qvw" ...

P.P.S.: Aha - I now have a pure AutoNumber() fct with an Auto-ID here - but I have an AutoNumber() fct, albeit without an ID, in another table - and the key is not unique anymore - when I select one instance of the internal key (which is identical to the external one), I get a link to data from another table ...

=> Bug?? According to the help_file, that should not happen

P.P.P.Pfff ... Now I have changed the other one so that both AutoNumber() functions have an AutoID and still the key is not unique ... in the table it is of course, but in the linktable a key from two different tables has the same value ...

[datanibbler]

Could the reason be that I am using the Autonumber() function in two different scripts? Does that maybe eliminate the "distinguishing effect" of the AutoID?

[swuehl · Accepted solution]

The second argument to Autonumber() can be used to create different symbol tables for the numbering, but this should not be your issue.

Autonumber() will just number the symbols created by the first argument as they appear in load order.

Hence it may not create the same number for the same symbol when called from different script runs with a different load order of the symbols.

[datanibbler]

Well, then that's my issue.

Or rather, I have the issue the other way round I guess - Autonumber() returns me the same number for altogether different symbols.

This is probably due to the same fact that I use it in different scripts. Apparently, that second parameter, the AutoID, does not work across scripts.

So I cannot really use that - or rather, I have to use a prefix to make it unique to one table, thus losing the advantage of a numeric key ...

Thanks anyway for explaining!

Best regards,

DataNibbler

[datanibbler]

What would you say then - it seems that I can use that, but it does not bring me that much advantage because I have to attach an alphanumeric prefix and so I end up with a string as key again. But that is still better than having a concatenated key with potentially several strings in it - or is it?

For instance, the outcome of my use of Autonumber is something like >> MC1, MC2, MC3 << and so forth.

It used to be >> 2018\02\Revenue\1100\P01\Current Year <<.

Is that any more resource-efficient and performant?

Thanks a lot!

Best regards,

DataNibbler

[swuehl]

The more key values and the longer the text values, the bigger the symbol table.

The main advantage of the autonumber'ed keys is that you get rid of the symbol table altogether.

If you want to use the autonumber'ed keys, than just create these keys in a single script run, e.g. when creating your complete data model.

Using autonumber'ed keys have the disadvantage that they are not giving you any insight into the underlying key structure (compare to your example: 2018\02\Revenue\1100\P01\Current Year) so you might keep this latter form for debugging and just replace it with an autonumber'ed key when you are finalising your model.

[Gysbert_Wassenaar]

I use the hash* functions to create keys that are always the same given the same input values. Those keys are big, so in the final front end applications I use the autonumber function to replace them with numbers. Those autonumbered keys won't be used between applications.