Bitmetric Friday Qlik Test Prep – Week 8 – Sales & Budget model – 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

This week’s question has triggered an almost unanimous response.

The correct answer is D: budgets cannot be shown for customers who haven’t bought anything

We can verify this by loading a small dataset and see what happens. In the following set we have two customers, A and B. Customer A has sales amounts of 50 and 100, and a budget of 200. Customer B has no sales and a budget of 200.

If we load this data into Qlik Sense and visualize it in a table we see the following result:

No alt text provided for this image

We can clearly see that there is no association between Customer ID B, on row 3,and theBudget Amount 200, on row 4. The reason for this is the way the data is modelled:

No alt text provided for this image

The CUSTOMER table is associated with the SALES table through the Customer ID field. In turn, the SALES table is associated with the BUDGET table through the Budget ID field.

As Customer ID B does not have any sales, it’s missing the ‘hop’ between the CUSTOMER and BUDGET table. Only once Customer ID B has entries in the SALES table will the data be correctly associated.

This issue is known as a ‘Chasm trap‘, where a model may suggest the existence of a relationship between entities (in this case, CUSTOMER and BUDGET), but the pathway does not exist for certain entity occurrences (in this case, Customer ID B).

OK, so the correct answer is D, but isn’t C also correct?

If you come from an SQL background you may expect that budgets get multiplied for customers who have multiple sales. This issue is known as a ‘Fan trap’ and it would be a correct assumption if we were to JOIN the tables together. In this data model however, that is not the case. This article by Henric Cronström explains it very well. Henric also gives an additional example of a Chasm trap, and a suggestion on how to resolve it.

How can we model this correctly?

Now that we know what the issue is, how can we model this data correctly so that customers, sales and budget are all correctly related? We’ll leave that topic for another time, although we’re certainly interested in your take on it 😉 We look forward to seeing your comments and hope to see you again next Friday!

No alt text provided for this image

Get in touch

Totally random comment, but did you know that you can also reach us via WhatsApp? Got a question? Want to work with us? Looking for some advice (or small talk) about Data & Analytics? Then don’t hesitate to get in touch!

Previous posts

Week 7: MonthEnd(Today())

Week 6: Looping Tables

Week 5: Set Identifiers

Week 4: Time series visualization

Week 3: Circular References & Synthetic Keys

Week 2: Section Access

Week 1: Optimized Load