Bitmetric Friday Qlik Test Prep – Week 6 – Looping tables – Solution

By Bitmetric Admin

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:

No alt text provided for this image

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:

No alt text provided for this image

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

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

No alt text provided for this image

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.

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

No alt text provided for this image

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.

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.

No alt text provided for this image

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:

No alt text provided for this image

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:

No alt text provided for this image

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?

PS. Did you know that we released version 2.1 of PinIt for Qlik Sense last week?

No alt text provided for this image

With PinIt, anyone can create self-service mashups using objects from all their Qlik Sense applications. That executive dashboard that you’ve been putting off because it needs data from 5 different apps just became a 10 minute job 😉 Best of all, it’s free to use for 5 users. Check it out.

Previous posts

Week 5: Set Identifiers

Week 4: Time series visualization

Week 3: Circular References & Synthetic Keys

Week 2: Section Access

Week 1: Optimized Load