How To: Prevent Circular Reference in a Qlikview Data Model

Posted 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.

picture1

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.

picture3

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.

picture4

The use of Link Tables
With 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.

picture2The 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_ID
RESIDENT Fct1;

CONCATENATE

load distinct
%KEY_FACT2_ID        AS %KEY_LNK_ID
,    %KEY_DIM1_ID        AS %%KEY_DIM_ID
,    %KEY_DIM2_ID        AS %%KEY_DIM2_ID
RESIDENT 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:
picture5
Bear in mind, use carefully, and don’t trust it is working 100% until you have verified your data -  this is experimental code.

  • John Lynn said,

    Just a newbie question: what do the % and %% mean in the field names? Is there a special significance to these, or is it just a naming convention that you use? Thanks for a great article…

  • william said,

    Your welcome John! It is naming convention we use for key / id fields. % is used for relation between link and fact table, %% is used for relation between link and dimension.

  • Hector said,

    just curious ..why you not use join instead of concatenate?

    hector

  • william said,

    Hi Hector,

    When having different facts you don’t want to merge the rows together. You can find an excellent example of the difference between the concatenate and join function at Qlikview Notes: http://qlikviewnotes.blogspot.com/2009/11/understanding-join-and-concatenate.html

    Please have a close look at the budget and sales example. It shows the different results of the join and concatenate function which is also suitable for the example in this post.

  • sailaja said,

    How we use last() in QlikView

  • william said,

    Hi Sailaja,

    I don’t know if I understand your question but you could search in the Qlikview help for the peek() or the above() / below() function..

  • Zubair Khan said,

    William

    I am facing same problem, like below
    Sales:
    SalesPersonCode,
    LineTotal,
    ItemCode,
    CustomerCode,
    DocDate
    in Sales Table,
    But I also need data from credit note file which contains following fields
    CreditMemo:
    SalesPersonCode,
    LineTotal,
    ItemCode,
    CustomerCode,
    DocDate

    How I can make model avoiding loop in data model? Here I want to explain that I published SalesPerson data from Master table and it works with both tables but it does not work with customer table which also populated from customer master table, why qlikview links with Sales Person’s talbe and why not customer table?

  • May said,

    Hello,
    this is useful article.
    Btw, Qlikview is automatically join table when field names are similar.

    How can I unspecified some fields (with the same name) not be the the key fields…

  • william said,

    Hi May,

    You could rename a field with as like in the example CustomerCode field below:

    Example:
    load
    CustomerCode as RenamedCustomerCode,
    LineTotal,
    ItemCode,
    CustomerCode
    resident
    example;

Add A Comment

Get Adobe Flash player