Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
datanibbler
Esteemed Contributor

Numeric keys instead of alphanumeric - always preferable?


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

Tags (1)
1 Solution

Accepted Solutions

Re: Numeric keys instead of alphanumeric - always preferable?

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

4 Replies
giakoum
Honored Contributor II

Re: Numeric keys instead of alphanumeric - always preferable?

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?

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?

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

datanibbler
Esteemed Contributor

Re: Numeric keys instead of alphanumeric - always preferable?

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

Community Browser