How to Fix Circular References & Remove Synthetic Tables

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

Learn how to use QlikView, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free QlikView Training Demo!

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
];

Bond:

Load* inline [
Bond ID, bond
1000, LA
1001, St louis
1002, Atlanta
1003, New York
1004, Detroit
];

Frequently Asked QlikView Interview Questions & Answers

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

Related Page: Script Editor Features And Commands In QlikView

Actual:

LOAD
Hash128(broker ID, bond ID) as
Actual forecast ID,    *
INLINE  [
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
];

Forecast:

LOAD
Hash128 (broker ID, bond ID) as
Actual forecast ID,  *
 INLINE  [
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 IDResident
Actual;

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
Resident
Forecast;

Related Page: QlikView Data Transformations In Flat Files

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: