Friday 24 January 2014

QlikView Functions: autonumber()

Having our new baby (AKA the mini QlikView addict) around has meant very little time for anything, let alone blogging. So in order to ensure I at least manage the odd post or 2 I thought it would be good to start a new series of short posts on different qlikview functions and their uses. To kick things off I have decided to take a look at the autonumber() function and the closely related autonumberhash128() and autonumberhash256(). All 3 functions do a very similar thing so let's look at autonumber() first and then consider how the other 2 functions differ.

Autonumber() can be considered a lookup function. It takes a passed expression and looks up the value in a lookup table. If the expression value isn't found then it is added to the table and assigned an integer value which is returned. If the expression value is found then it returns the integer value that is assigned against it. Simply put, autonumber() converts each unique expression value into a unique integer value.

Autonumber() is only useful within the QlikView script and has the following syntax:

autonumber(expression [, index])

The passed expression can be any string, numeric value or most commonly a field within a loaded table. The passed index is optional and can again be any string or numeric value. For each distinct value within the passed index, QlikView will create a separate lookup table and so the same passed expression values will result in a different returned integer if a different index is specified.

So how exactly are the 3 autonumber functions different? Autonumber() stores the expression value in its lookup table whereas autonumberhash128() stores just the 128bit hash value of the expression value. I'm sure you can guess therefore, autonumberhash256() stores the 256bit hash value expression value.

Why on earth would I want to use any of these functions? Well the answer is quite simply for efficiency. Key fields between two or more tables in QlikView are most efficient if they contain only consecutive integer values starting from 0. All 3 of the autonumber functions allow you to convert any data value and type into a unique integer value and so using it for key fields allow you to maintain optimum efficiency within your data model. 

A final word of warning. All 3 of the autonumber functions have one pitfall, the lookup table(s) exist only whilst the current script execution is active. After the script completes, the lookup table is destroyed and so the same expression value may be assigned different integer values in different script executions. This means that the autonumber functions can't be used for key fields within incremental loads.  

3 comments:

  1. Nice, simple explanation. Can you clarify how autonumber function deal with null values. I had some issues recently and resorted back to using the original values where I had one table with nulls (which were assigned a autonumber of 0) and I was trying to join to another table that had no null values.

    ReplyDelete
  2. Hi Mike
    First of all, how autonumber() will behave depends on if it is a true null or a zero length string. For true nulls, the result of the autonumber() will also be null. You can prove this by running the following script:

    LOAD autonumber(null()) AS field AUTOGENERATE 1;

    By adding "field" to a list box you will see no records. Being a true null and the fact that null values don't really exist in QlikView (they are simply the absence of a value) means that when using it for a key field, QlikView will not associate a null in one table to a null in the other.

    For a zero length string, autonumber() will assign it a value as it would any other passed value. The number assigned to the zero length string will depend on the order it appears in the values that are passed to autonumber(). You can see this by running the following script:

    LOAD autonumber('') AS field AUTOGENERATE 1;

    The result will be a single value in "field" of "1". Autonumber() is 1 indexed and so I'm not sure where you are getting your 0 value back.

    Regards
    Matt

    ReplyDelete
  3. Matt, I know the pitfalls of a young family when it comes to getting round to blogging about QV. The fact that the three autonumbers only work within a single execution is a bit of a biggy because the types of app that need the boost of an efficient join are also those that really need incremental loads for performance. Always saddens me that I can't use these functions more often.

    Keep up the blogs mate, really useful stuff
    Dave

    ReplyDelete