How To: Prevent Circular Reference in a Qlikview Data ModelPosted by William on 08/10/2009 under How To | 9 Comments to Read

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

,    MeasureFrom……

CONCATENATE

load‘Fact2’        AS Facttype,    ID            AS %KEY_FACT2_ID,    MeasureFrom……

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.

The use of Link TablesWith the use of link tables, it’s possible to keep the facttables separated from each other. The advantage of this solution is that there is no need for set analysis when doing calculations for fact 1 or fact 2 when they share the same measure. Another reason for choosing this method is to keep the datamodel a logical one.

The link table contains the key fields of the facts and dimensions. It is likely that the two fact tables don’t have the same keyfields. We can use both unique key fields and give them the same fieldname in the linktable to create a unique id for each record. Because Qlikview loaded all necessary keys in the facttables, we can simply use a resident load. Below shows an example of a linktable. As you can see, I use %% to indicate it’s a key for the dimension and % for the fact.

//**************************************************************************// LINK TABLE//**************************************************************************LNK_TABLE:load distinct%KEY_FACT1_ID        AS %KEY_LNK_ID

,    %KEY_DIM1_ID        AS %%KEY_DIM1_ID,    %KEY_DIM2_ID        AS %%KEY_DIM2_IDRESIDENT Fct1;

CONCATENATE

load distinct%KEY_FACT2_ID        AS %KEY_LNK_ID,    %KEY_DIM1_ID        AS %%KEY_DIM_ID,    %KEY_DIM2_ID        AS %%KEY_DIM2_IDRESIDENT Fct2;

Automated Link script

In the share Qlikviews section of the QlikCommunity you can find a script from Lars Christensen that automatically creates a linktable and removes synthetic keys from “any” combination of tables. Using this code in our example brings the following model:

Bear in mind, use carefully, and don’t trust it is working 100% until you have verified your data -  this is experimental code.

test: LOAD ID,      CreatedDate as LinkDate,      CreatedDate,      DepartureDate

RESIDENT Data; CONCATENATE (test) LOAD ID,      DepartureDate as LinkDate,       CreatedDate,      DepartureDate RESIDENT Data;   drop table Data;   // create a calendar   load Date AS  LinkDate,       month,       year,       Days from Calendar.qvd(qvd);   above transactions both will be concatenated and association with Calendar

now. please check and inform to me