Fixing Circular References And Removing Synthetic Tables in Qlikview

Fixing circular references and removing synthetic tables in Qlikview:

The good thing about the Qlikview data model is that tables get automatically connected to each other based upon certain key names in there, called as ‘associative data modelling’. This feature is very useful, but produces certain structures which are not considered good & degrade the report performance. One of them is these unwanted structures known as circular reference. This logical loop, i.e. circular reference occurs when there is two or more path of association among three or more tables, i.e. from one table to another can be reached via multiple paths.

Circular references and synthetic tables in Qlikview can be corrected in a many different ways. Few of them are:

  • Concatenating two tables together
  • Renaming data fields
  • Creating a link table

We have discussed renaming data fields  script features and functions, (rename, as, alias, and qualify). Concatenate is discussed in a section, “Table combining and concatenation”, later in this chapter. In this section, we will describe how to use a key table (also called as link table) to fix circular references. Key tables work best when you don’t want to use the concatenate function, as and when you wish to leave the tables intact and separated. Note that concatenate should, typically, only be used to fix circular references when all identically named facts (or dimensions) in the tables to be concatenated indeed have exactly the same measures or attributes.

Here’s a quick example of a data model using a financial services company. In the first section, we do an inline load of brokerages, ID’S and names to create the broker agency table, then an inline load for the bond ID’S and municipal bond names to create the bond table, as shown in the following code:

Broker agency:

Load * inline [

  BrokerID, brokeragename

100, FBSbrokerage

200, wellsfargo

300, UBS

400, nothgeorgia



Load* inline [

Bond ID, bond

1000, LA

1001, St louis

1002, Atlanta

1003, New York

1004, Detroit


 We will create the tables for actual and forecast. Since these two tables will automatically create a synthetic join-as they share two identical data field names. We will create our own key and key table to have complete control over how the script reacts.


The following code, starting with hash128, is where we create a unique key from the two shared keys in the table, broker ID and bond ID. Here, we could have used autonumber, auto number hash128, or autonumber hash256, but we prefer using hash 128 for a good compromise of low collision probability and probability when using shared QVD instances for several qlikview applications (in other words, the key will work reliably).



Hash128(broker ID, bond ID) as

Actual forecast ID,    *


Date ID, broker ID, bond ID,

Actual sales

269,  100,  1000,  5000

269,   200,  1001,  10000

269,   200,  1002,  40000

270,   300,  1003,  33000

270,   200,  1004,  25000

271,   400,   1004,  45000

271,   100,   1001,  300000




Hash128 (broker ID, bond ID) as

Actual forecast ID,  *


Broker ID,  bond ID,

Forecast sales

100,   1001,  150000

200,   1001,  10000

300,   1003,  30000

400,   1004,   50000


 We will name it with a descriptive name (we prefer adding the word bridge in the name), and start with adding fields from the resident table, ‘actual’, that we just added, as shown in the following code:

Actual forecast bridge key:

Load distinct

Actual fore cast ID,

Broker ID,

Bond ID



Then, we continue adding to the new key table from the other resident table, forecast, by doing a join on the new table(actual forecast bridge key), and load the data fields shared between the two fact tables, including the new key (actual forecast ID)

Join (actual forecast bridge key)

Load distinct

Actual fore cast ID,

Broker ID,

Bond ID



Because we added the two shared data fields into the new key table, we can drop them from the two original fact tables

DROP fields broker ID, bond ID

From actual;

DROP fields broker ID, bond ID

From forecast;

The final result is a link table, instead of a synthetic table diagram:


Enroll for Instructor Led Live Qlikview Training 

0 Responses on Fixing Circular References And Removing Synthetic Tables in Qlikview"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.