topic Re: Numeric keys instead of alphanumeric - always preferable? in QlikView

Numeric keys instead of alphanumeric - always preferable?

datanibbler — Fri, 13 Feb 2015 15:35:52 GMT

Hi,

from several blog_posts I've read from HIC and others and from the literature I know that numeric keys are generally preferable to alphanumeric keys to link tables because QlikView is much better at storing numeric characters in a small memory space.

However, I have been wondering about one thing.

It may be that the answer to this is somewhere among the XX answers and comments to the blog_posts, so I might have overlooked it. Forgive me in that case for repeating the question here.

Say I have two tables which I can only link using an Item_ID, which contains alphanumeric characters.

=> So it would generally be a good idea to replace that using the AutoNumber() function and have a numeric key instead.

<=> But what if I still need the original alphanumeric field to be displayed? So I would still be carrying that alphanumeric key around, even if I renamed it to avoid linking the two tables by that ... (that is the case, I think, in most apps we have that are using this kind of alphanumeric fields)

=> Would it still help to reduce my app's memory_profile to "repeat´" that field using the AutoNumber() function?

Thanks a lot!

Best regards,

DataNibbler

Re: Numeric keys instead of alphanumeric - always preferable?

giakoum — Fri, 13 Feb 2015 15:41:27 GMT

Hi DataNibbler

the only reason I can see that you would keep a key like that is because you show it in the front end. But why would that key be useful to any user using your application? A key does not (or should not) mean anything to business users.

Anyway, I would link the tables using autonumber and keep the alphanumeric key only once.

Hope this helps!

Re: Numeric keys instead of alphanumeric - always preferable?

alexandros17 — Fri, 13 Feb 2015 15:42:32 GMT

Qlik stores data in a very efficient way so do not care about memory, above all if you are speaking of a key field, I agree that numeric keys are better but this is a generic rule, in your case I should use original alphanumeric format because you can sort, link and group in the same way of a numeric field. If you should have ID and Name for example it should be better to transform in a numerical representation (Autonumber or autonumberhash)

hope this helps

Re: Numeric keys instead of alphanumeric - always preferable?

marcus_sommer — Fri, 13 Feb 2015 16:12:18 GMT

Hi DataNibbler,

if you no really a performance problem you shouldn't try anything. If an improvement of performance necessary you will need some efforts to try this and that and check with the mem-files the calculation times and the table/field-memory then it will depend on various things if you could create significant improvements with manageable efforts or not.

If you could use autonumber-fields as keys and don't need the alpha-numeric values you should use them (because key = pointer). If you need these alpha-numeric values you could try to split these field into several fields to reduce the number of unique values and/or you could find some logic to replace the strings with numbers within this field and if you used it within the gui you made this replace backwards.

A good starting point is here:

Mastering QlikView

Stephen Redmond

ISBN-13: 978-1782173298

- Marcus

Re: Numeric keys instead of alphanumeric - always preferable?

datanibbler — Sun, 15 Feb 2015 14:27:04 GMT

Hi Marcus,

I have just bought this book in one of packtpub's promotions and I'm just starting to read it on my eReader. Unfortunately I have very little time to ever read anything, but it surly is a very good book and I enjoy learning that kind of things.

I think you're right - as long as there are no issues and no one complains about apps being too big, I won't mess around with them.

In cases like that I do need the alphanumeric field both as key (it cannot be split, either, or it wouldn't uniquely identify a record) and on the GUI - because the item_nr. is something that is always needed - not for the overview, but the whole point is the middle_managers being able to retrace anything out-of-the-ordinary.

Thanks a lot!

Best regards,

DataNibbler