4 Replies Latest reply: Feb 15, 2015 9:27 AM by Friedrich Hofmann RSS

    Numeric  keys instead of alphanumeric - always preferable?

    Friedrich Hofmann


      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?
          ioannis giakoumakis

          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?
            Alessandro Saccone

            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

              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?
                  Friedrich Hofmann

                  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