Auto AutoNumber your key fields

By Barry Harmsen

A quick note on a cool feature that I recently learned from Oleg Troyansky: AutoNumber.

I hear you thinking: “What? You didn’t know about AutoNumber? It’s been around for years. Hand over your Luminary badge!“. Admittedly, this is also what I thought when I hear Oleg mention it but it turned out that I misunderstood. Oleg wasn’t talking about the AutoNumber() script function, but the AutoNumber script statement. This statement was added in the June 2018 release of Qlik Sense (as well as QlikView 12.20) and lets you AutoNumber multiple fields, across multiple tables in a single line of script. And no, despite it being available for over a year, I hadn’t heard about it before. I’ll certainly be using it going forward though!

AutoAutoNumber

I’m sure that Oleg (or Rob) will discuss potential use cases and performance implications in a future blog post and that it will be touched upon in our Masters Summit for Qlik materials.

For now I just want to share a quick hack that I cooked up this Friday; AutoAutoNumber. If you call this subroutine at the end of your script, it will automatically discover all key fields in your data model and apply AutoNumber to them:

What I like about it is that you can easily check your keys during development/maintenance and don’t have to manually enable AutoNumbering when you’re getting the app ready for production. Also, when key fields are changed or added, they’ll be automatically picked up.

You can find the full subroutine here. I am very interested in hearing your opinions about it, and any potential improvements you may see.

Update: Youri Torchalski points out in the comments below, that you can also use a wildcard with the AutoNumber script statement. For example: AutoNumber “*_Key”; If you are using a consistent naming convention for your keys, and that is always a good idea, then you don’t need to use the subroutine. Thanks to Youri for pointing this out!

Now that you’re here

If these are the sort of tips and tricks you enjoy, then you’ll love the Masters Summit for Qlik. In 3 days, we’ll take your skills to the next level and help you get more from your investment in Qlik. We do this not only by training you, but also by providing you with a wealth of ready-made examples, tools, utilities and drop-in solutions that you can start using immediately. Additionally, with many Qlik enthusiasts there and ample opportunities to network, you will not only go home with new skills and ideas, but also with valuable new professional contacts.

We have events coming up in Amsterdam and Washington DC, currently still with an attractive $300 early-bird discount (ends October 4th 2019). See you there?

Masters Summit for Qlik - Amsterdam - Washington DC

About The Author

Barry Harmsen

Hi there, I'm Barry and I'm a Business Intelligence Consultant at Bitmetric and based in the Netherlands. Originally from a background of 'traditional' Data Warehousing, Business Intelligence and Performance Management, for the past 10 years I have been specializing in Qlik and a more user-centric form of BI. I have done numerous QlikView and Qlik Sense implementations in many different roles and industries. In 2012 I co-authored the book QlikView 11 for Developers. You can follow me on Twitter at @meneerharmsen.

13 Comments

  • 1
    September 27, 2019 - 18:03 | Permalink

    Hi Barry,

    Cool function indeed. I read it one time but it did not occur to me that it was a different function than the initial Autonumber.

    I think the cool feature of this function is the wildcard function. I think (if I understand it correctly) you can easily reduce or completely leave out your subroutine if you use a prefix for all key fields. I always use _Key e.g. so if I understand it correctly (because I did not try it in practice yet) I could use the following small syntax at the end of the script without using the subroutine.

    AutoNumber _Key*;

  • 2
    September 27, 2019 - 19:24 | Permalink

    Hi Youri,

    You’re right 😉 If you use a good naming convention, and that is highly recommended, then you don’t need to use the subroutine but can use a wildcard. I missed that when I read the docs before and guess I got too caught up in my Friday afternoon tinkering 😉 Thanks for bringing this to my attention.

    Kind regards,
    Barry

  • 3
    Andy Burns
    September 27, 2019 - 21:56 | Permalink

    Hi Barry,

    What is the performance impact using this function? Still on a much older version ourselves. Some apps we use have up to 100m rows, but we need to be careful balancing app performance with refresh speeds

  • 4
    September 28, 2019 - 05:53 | Permalink

    Oleg has told me he’ll go more in-depth in an upcoming blog post. Just as an example though, if you’re loading data from QVDs, using the script statement instead of the AutoNumber() function will let you maintain an optimized load.

  • 5
    September 28, 2019 - 09:08 | Permalink

    I thought about it later but the nice thing about your subroutine is that you can apply it on global data models that do not have a naming convention. It is very easy to use for performance enhancement and standardization purposes. Very nice, well done!

  • 6
    September 30, 2019 - 18:39 | Permalink

    When I remember correctly, autonumber only saves memory but does not bring any CPU performance boost.

    Have you done any testing lately, or is Karl Pover’s blog post still up to date:
    http://poverconsulting.com/2014/01/30/is-autonumber-really-worth-the-hassle/

    Thx,
    Roland

  • 7
    September 30, 2019 - 19:53 | Permalink

    Hi Roland,

    No, I’ve not done any performance testing, but you are right, the main technical benefit of AutoNumber is reduced memory consumption.

    Now whether you should use AutoNumber at all is an “it depends” question. From a technical point of view, depending on the sort of key used you may decide to use it or not. Sequential integer: maybe not. High cardinality GUID strings, as in the example above: Yes please! AutoNumber also adds overhead during script run-time, so maybe you want to avoid it for time-critical applications altogether, no matter the memory cost.

    Assuming that you have a use case where AutoNumber is valid. IMHO, the main ‘hassle’ with the AutoNumber() function is that you have to apply it in each LOAD statement. It’s also inconvenient to switch between using the regular key during development/troubleshooting and the AutoNumber() key when moving an app to production.

    The introduction of the AutoNumber script statement negates the hassles mentioned above, and from that point of view it’s interesting to be aware of it and maybe reconsider its use. Including a single line of script is not much of an inconvenience. It might suddenly become interesting to use AutoNumber in edge cases where you wouldn’t bother with it before.

    I’m an “it depends” guy, so I don’t believe in absolute “best practices”. I do like the thought of “good ideas” though. One such “good idea” that I can get behind, especially as self-service BI becomes more prominent, is that keys are not data, and should not be exposed in the front-end. Replacing keys with a meaningless AutoNumber integer (along with HidePrefix etc.) makes it easier to enforce this. This combined with the added potential for reduced memory consumption makes that AutoNumber is a part of my “default” approach. And finding out about the AutoNumber script statement has just made that a whole lot easier for me.

    Kind regards,
    Barry

  • 8
    Karl
    October 4, 2019 - 16:45 | Permalink

    Man, I regret that post. I’ll have to update it.

    Like Barry says, this was before there were techniques available to quickly autonumber all key fields. These techniques make it so easy to turn off the autonumber if you need to debug any issue with them, so that’s no reason not to do it.

    Also, I don’t think it’s possible, but would there be any way to use the $key tag within the script to identify key fields?

    Karl

  • 9
    Karl
    October 4, 2019 - 17:10 | Permalink

    One more comment. I don’t think you need to explicitly declare a namespace in your subroutine. You only need it when identical values in different fields share the same number.

  • 10
    Zak
    October 7, 2019 - 16:48 | Permalink

    Hi,

    I’m still fairly new to Qlikview, so would like to play around with the sub routine to see what it does. Should the sub routine work in Qlikview (using 12.20)? I’ve added it at the end of my script, then added Call AutoAutonumber. Qlikview seems to have a syntax issue with the line below (row 50):

    AutoNumber [$(AutoAutoNumber.AutoNumberField)] USING ‘$(AutoAutoNumber.i)’;

    Not sure if it should work, or just isn’t working!

    Thanks.

  • 11
    Stéphane
    October 8, 2019 - 12:51 | Permalink

    Hi Barry,

    Actually this new AutoNumber as a statement feature seems to not be available in QV 12.20. It’s the version I use and this statement is not recognized. Sadly.

    Thanks anyway for this interesting post.

    Stéphane

  • 12
    October 9, 2019 - 11:36 | Permalink

    Hi Stéphane,

    I have to check, I understood it was available since 12.20 but thinking about it now, it makes more sense for it to be introduced in 12.30 (as 12.20 was released in November 2017, and this was introduced in Sense in June 2018).

    I’ve just tested it in 12.30 SR9 and can confirm the AutoNumber script statement works there.

    Kind regards,
    Barry

    • 13
      Stephane
      October 10, 2019 - 08:41 | Permalink

      Hi Barry,

      Ok ! That’s a very good news. As I told to your Masters Summit colleague Oleg, I don’t find any trace of this new feature in any release notes of version 12.20 to 12.40. I thought it was a Qlik Sense feature only so I’m happy about your test on version 12.30 SR9.

      Thanky a lot !

      Stephane

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Read previous post:
    Intro to Qlik Sense Mashups

    Hi all, I'm Bernard, and in this post I will introduce myself to you and tell you more about the...

    Close