Qlik table functions and indexes

Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question about looping across tables to drop them:

Realistically, it’s unlikely that a question like this will appear in an actual Qlik certification exam, but it’s a nice brain teaser nonetheless. It was good to see that many of you got the right answer!

The correct answer is D: the script fails in the sixth iteration of the loop

First, let’s look at what the script is supposed to do:

• Loop over all resident tables.
• For each individual table store it to QVD and subsequently drop the table.
• Move on to the next iteration of the loop (and the next table).

At first glance, this is exactly what the script does. However, when we actually run the script the result is:

So what’s going on here? Let’s take a closer look at the functions we’re using in the script.

Qlik table functions

Table functions can be used to retrieve metadata about tables and fields in your data model. In this script, we’re using two table functions:

• NoOfTables() to retrieve the amount of tables currently residing in memory
• TableName() to retrieve the name of a table associated with a particular index number

The issue: when tables are dropped or created, the table list is updated

At the same time the index is increased by one in each iteration of the FOR loop. The diagram below shows how this plays out.

After the first loop, Table_0 is dropped. This causes all tables to shift forward one position in the table list. Table_1 now occupies the place that Table_0 was in before.

The index i is increased by one for the next iteration of the loop. Fetching TableName(1) returns Table_2, which is stored and dropped. This shifts all remaining tables forward one position again, after which the index i is increased by one again, and so on. (note that we’re also skipping a table each iteration)

This continues until the sixth loop. In this iteration the remaining list of tables is shorter than the index value i and TableName(5) returns null because there is no table in that position. As vTable is now an empty string, the script fails when it tries to store the non-existent table.

So how do we fix this? Turn it around!

We can solve this issue by starting at the end of the table list instead of the beginning. As only tables that come after a dropped table move forward in the table list, always dropping the last table in the list ensures that all the other tables keep their index position.

This only requires a small tweak to our original script:

Another alternative is to always fetch the name of the table at the beginning of the list, as a new table will be shifted to the beginning of the list in every iteration:

While also a valid solution, in terms of readability (and therefore maintainability) we much prefer the first solution.

That’s it for this question! See you next Friday?

More from the Bitmetric team