Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
When designing a datamodel in Qlikview, circular reference is quite a common thing.
There are several solutions to prevent such loops like the concatenate function or link table. Although the best solution to fix circular reference depends on the situation, I personally prefer the link table most of the times. When you have two fact tables for example, which shares more then one dimension, it means there is circular reference.
To prevent this situation we could use the concatenate function to create one major facttable. You could consider this solution when both tables have many similarities. When using the concatenate function, always dwell on the fact that, if the measure fields in both facttables are the same, calculations will cover all records of the table. Of course there is the possibility to create a field and specify the facttype so you can select ‘Fact1’ or ‘Fact2’ and use set analysis in your expressions but I think it’s clear that this isn’t an easy solution when the records always need to be separated in all expressions.
Example 1: Concatenate two fact tables.
//**************************************************************************
// FACT TABLE
//**************************************************************************
FACT:
load
‘Fact1’ AS Facttype
, ID AS %KEY_FACT1_ID
, Measure
From……
CONCATENATE
load
‘Fact2’ AS Facttype
, ID AS %KEY_FACT2_ID
, Measure
From……
What about concatenate if the fields in the facttables are quite different? Rationally this does not seem to be a good idea is it? Let’s demonstrate this with an example of two different processes. The facts are:
- The purchase of products
- The selling of products
When loading the original datamodel, Qlikview has to create a synthetic key because the dimensions date and product are shared. Although the synthetic key in this situation works fine, it’s better to prevent this.
When we concatenate the purchase and selling table, the synthetic key is gone and the problem is solved. Allthough naturally this just doesn’t feel like the ‘right’ way, there is no difference for the end user because the interface of Qlikview works on attribute level. For Qlikview, this is best practice and a good way of solving circular reference. Keep in mind that, for solutions with many tables, the overall picture for the developer is getting less clear. In that case it’s better (in my opinion) to use link tables.
