14 February 2022

Qlik table functions and indexes

Share this message
Looping tables in Qlik can be tricky. Learn about indexes and how dropping tables affects them.

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:

A Data Architect uses the following script (figure 1) on a data model with 10 tables. What is the result of running this script?

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:

Qlik error. Table not found.

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

To illustrate this, have a look at the diagram on the right. This shows the 10 tables we have in our data model, along with their index.

Qlik uses a zero-based index, so for the sake of clarity we’ve named our tables Table_0 through Table_9. This way, the name of the table corresponds directly with its position in the table list. TableName(0) returns Table_0, TableName(1) returns Table_1, and so on.

As there are 10 tables in our data model, the NoOfTables() function returns the value 10.

Qlik tables and table indexes

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

The issue with this script is that we’re not only storing tables, we’re also dropping them. Whenever a table is dropped, all the tables that come after it in the table list shift forward one position.

The image on the right shows what happens when we drop Table_0; all tables shift one position forward in the table list. Now TableName(0) doesn’t return Table_0, it returns Table_1.

Table indexes in Qlik get updated after a table is dropped

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.

What happens in Qlik when you drop tables in a loop, starting at the lower bound of the index.

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 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 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:

Updated Qlik script for dropping tables in a loop

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:

Another way to drop tables in a loop in Qlik Sense or QlikView.

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?

Take your Qlik skills to the next level!

Since 2013, the Masters Summit for Qlik is the premier advanced training for Qlik. Join us in Vienna and take your Qlik skills to the next level.

Join the team!

Do you want to work within a highly-skilled, informal team where craftsmanship, ingenuity, knowledge sharing and personal development are valued and encouraged? Check out our job openings.

Friday Qlik Test Prep Script Solution

How can we help?

Barry has over 20 years experience as a Data & Analytics architect, developer, trainer and author. He will gladly help you with any questions you may have.