Hi there! Long time no talk… As you can imagine, I’ve been busy – Master Summits for Qlik, my own “QlikView Your Business” master classes, etc etc etc…
Plus, it’s not too often that we see something so interesting and so exciting that warrant a blog article. Maybe I’m not looking hard enough…
Anyway, speaking of exciting things – are you familiar with AUTONUMBER? Yes, yes, that wonderful function that replaces long strings with short numeric values by numbering the strings sequentially? Of course you know about it! Everybody knows the benefit of using AUTONUMBER – smaller numbers are lighter than long strings, which leads to memory savings.
But wait, there is more! Many of you also know that the most important benefit of the AUTONUMBER function is the fact that it generates sequential integers – a sequence of whole numbers that doesn’t have any holes in it. Why is this so important, you may ask? Because this way, Qlik doesn’t need to store the Symbols for this field.
Now, if you don’t know what Symbols are in Qlik jargon, then you should definitely drop everything and check your schedule at the end of October or in the beginning of November. That’s when our next sessions of the Masters Summit for Qlik are going to take place – October 28-30 in Amsterdam, Netherlands (Amsterdam, Carl!), and November 6-8 in Washington, DC. There, you will learn all about Symbols, and why they are so important to know for developing high-performing and scalable Qlik Apps. Not to mention Advanced Data Modeling, Scripting, Visualizations, Set Analysis and AGGR, and an introduction to Extensions. A full bag of tangible knowledge, delivered by the best experts!
However, I digress. We talked about AUTONUMBER generating sequential integers. There is a caveat to that, and not every developer out there knows about it – in order to generate sequential integers for multiple fields, these fields must be qualified with their unique name spaces. So, instead of the simple form:
AutoNumber(KeyField1) as KeyNum1,
AutoNumber(KeyFIeld2) as KeyNum2,
we should be using an extended form:
AutoNumber(KeyField1, 'Key1') as KeyNum1,
AutoNumber(KeyField2, 'Key2') as KeyNum2,
“So far, nothing new or exciting”, – you might be muttering to yourself right now. But wait, there is more! Good things come to those who wait…
The good ol’ AutoNumber function comes with its own challenges though. First, it’s somewhat a pain to add it consistently to all the key fields and not mess it up at least once. Second, it’s known for being quite slow. Applying this function to several concatenated keys will certainly slow your load down. And, since we usually add AutoNumber to our scripts after we test and troubleshoot any possible issues, it always comes as a tedious chore at the end – to go through your whole script and add those darn functions to all the keys…
If only we could tell Qlik to apply AutoNumber to some of our fields at the end of the process, right?
Right, and here is the exciting news for you. Starting in June 2018 (both in QlikView and Qlik Sense), in addition to the AutoNumber function, there is a new AutoNumber command, which allows you to do just that – tell Qlik to apply AutoNumber to some of your fields. Just like this:
AutoNumber KeyField1, KeyField2;
Or, you can use an extended format, like this:
Autonumber KeyField1, KeyField2 USING '
Now, how cool is that?
At first glance, it sounds like we should always use the optional “name space” parameter, just like we do in the AutoNumber function. However, my testing shows the opposite. Using the shorter form of the AutoNumber command generates perfect sequential integer keys, and it happens very fast – virtually in no time. It feels as if Qlik simply dropped the Symbols for the specified fields and used the index instead.
On the other hand, the AutoNumber with the USING setting, took some time to get performed. It feels as if Qlik had to rebuild the index from scratch. Perhaps, it allows developers to place multiple key fields in the same “name space”. I’m still trying to figure out why someone would want to do that, knowing that this option will not allow as much memory savings as the simpler option. If you can find a compelling reason to do so, please tell me about it in comments below.
When I shared the news with my colleagues from the Masters Summit team, Barry Harmsen got so excited that he immediately created a little utility that allows you to automatically AutoNumber all of your key fields. You can find Barry’s article here. Next, Barry discovered that the AutoNumber command allows wildcards, which makes his utility… er, less-than-super-useful… “Oh well, it was still a cool exercise to do!” – said Barry in response.
So yes, you can AutoNumber all of your key fields at once, especially if you use any kind of a consistent naming convention for your keys. For example:
AutoNumber '%*'; – if your keys fields always begin with the %-sign.
AutoNumber '*_Key'; – if your key fields always end with the word ‘_Key’
Notice that you should use quotes when using wildcards for your fields.
That’s it, boys and girls! These are all the exciting news I have for you today – well, other than reminding you that the next Master Summit sessions are coming soon to Amsterdam and to Washington, DC. And, in addition to our “traditional” track for QlikView and Qlik Sense developers, we are excited to introduce a new “API track” for Web developers that want to extend Qlik Sense functionality with extensions, mashups, apps that use the Qlik Core platform, and more. This class is one of a kind, and it’s delivered in parallel with our traditional Qlik content.
At this time, the early bird pricing has expired, but for the readers of this article, I got an agreement with the Masters to honor the Early Bird Price for two more weeks. Use the promo code AUTONUMBER during registration to get the Early Bird price until October 21st. See you at the Summit?