What are the common methods to deal with circular reference in Qlikview?




I have started working on QlikView with multi tables data model and recently encounter with a error called “circular reference”. Reason for this is “similar name of variable in multiple table” so one of the method to deal with it is renaming common variable(s) and it will break the link among the tables.

But, I want to establish a relation between tables based on these common variables. Please suggest the methods to deal with circular reference while having associations(link) among tables.

Any help would be appreciated!

Thanks, Karan



You are right circular reference arises due to a circular link between multiple tables. When the data model becomes more and more complex, with a larger number of tables, and more and more destination points, we might also get to a point where we have more than one route connecting point A to point B. Well, in our case, we must always have one road between any two points. Otherwise, we would be having a circular reference.

Look at the above snapshot, here you can see that we can get to the Main Data table to the Calendar table either directly, through the YearMonth route, or by going first to the Promotions table and then moving to the Calendar table.

It can cause the performance of Qlikview application. We can go with the multiple methods to deal with this challenge like:

  1. Removing common fields: When common fields causing circular references are not required in data model and doing so will not affect the relationship between two tables. Removing fields can be done by commenting or removing field from load script.

  2. Renaming fields: When common fields causing synthetic keys are not same field (not containing similar values), These are actually different fields with same name. Renaming can be done by using “AS” or by using QUALIFY statement.

  3. Composite Key: We can create an explicit composite key with the concatenation of all common fields that actually represent the link between tables. After creating the new complex key, we can remove the conflicting fields from either table.

  4. Concatenate: Qlikview automatically concatenate/ combines tables if they have same granularity and columns. However, in some scenario, some of the columns are different. Here we need to force concatenation using CONCATENATE and combine the datasets in a single table.

  5. Link Table: Link table links two or more fact tables by taking all common fields out of the original tables and places them into a new table.The new link table contains all possible combinations of values for the set of fields through a unique key and is associated with the original tables.

You can refer below two articles to understand more about these methods:

Hope this helps!