QlikView Circular Reference

QlikView Circular References are encountered during data analysis and may lead to incorrect output. In this QlikView Circular References tutorial, we will be discussing what is Circular Reference, How it can affect the data analysis, and what are the major ways to resolve it. So, let's begin with this tutorial.

What is Circular Reference in QlikView?

Circular Reference is the loop that gets generated in the QlikView data models when three or more tables can be associated with each other through two or more paths or ways. QlikView supports an associative data model and automatically links two or more tables if they share common fields. But if there are two or more common fields, it creates ambiguity and distortion in the logical structure of data models which degrades the performance. Therefore, it is important to avoid the formation of the circular loop as much as possible.  

Want to Become an Expert in QlikView? Then visit here to Learn QlikView Training Online.

Let us understand the concepts more deeply with the help of tables. 

Suppose we have loaded three tables with the following columns in QlikView’s Memory-

TABLE1. Manager

• Deptmt

• EmpName

• Manager

TABLE2. Dept

• Deptmt

• EmpNo

• DepID

• Country 

TABLE3. Emp

• EmpName

• EmpNo 

• EmpSalary

Have you found any similarities in the tables? Yes, the names of the columns are matching in tables that create a loop. Let’s see how. 

 

Related Article: Qlik Interview Questions

From the above image, you can see that TABLE1 -Manager is connected with the other two tables in a loop due which should be avoided. Because in a data structure, if we can traverse a table in more than two paths it might lead to the ambiguities in the data interpretation. 

That is why, after creating a table with these columns, a box will pop up on the screen showing an error and telling that circular reference is formed. You can see in the below-given image to understand more clearly. 

Now, you must be thinking about how to resolve circular references in QlikView. Is it possible to avoid it or not? Of course, we can remove this condition in various ways. 

Related Article: What is Qlikview

What are the Ways to Remove Circular Reference in QlikView?

There are 3 major ways by which we can resolve the condition of the circular reference and they are as follows- 

• Comment

• Rename

• Remove

• Concatenate

• Link Tables

Comment

Let us start with the first method - Comment. This is one of the best and easiest way to resolve the circular reference. In this method, you have to just comment the matching column with the forward-slash (//) name as shown below.

Rename

In this method, you have to just rename the column name ie. substitute the matching column name with some other name. For eg. change EnpName as EmployeeName. After doing so, the name doesn’t match and therefore, circular reference conditions will not occur.

Remove

In this method, you can simply remove the matching column name from the table that is causing the circular reference to occur.

Concatenate

In this method, we use the concatenate function to merge the repetitive column name and form a single table out of them removing the circular reference. 

Link Tables

This method is used when there are multiple tables with distinct fields. First of all, these tables are connected to each other by adding a specific code then the composite key is formed by aggregating keys from the tables. After that, a new table is formed using the composite key and other common fields, and the original fields from each table are dropped. 

Related Article: Qlikview Synthetic Keys

How to Fix or Remove Circular References

QlikView gives automatic solutions when encountered with a circular reference; it refers to a loosely coupled table to more than one table.

Let us consider the following Input Data:

AutocadCourse:

LOAD StudentID

StudentQualification

StudentName

FROM

(D:QlikView1data1Autocad_Course.csv)

(txt1, codepage is 1356, embedded labels, msq);

JavaCourse:

LOAD StudentID

StudentAddress

StudentName

FROM

(D:QlikView2data2Java_Course.csv)

(txt1, codepage is 1455, embedded labels, msq);

VLSICourse:

LOAD StudentID

StudentAddress

StudentName

FROM

(D:QlikView3data3VLSI_Course.csv)

(txt1, codepage is 1576, embedded labels, msq);

Now, in the above input data, we have identical column names through which circular references will have arisen. To solve this problem, we will rename some of the column names.

Related Article: QlikView Data Model

AutocadCourse:

LOAD AutocadStudentID,

StudentQualification,

StudentName

FROM

(D:QlikView1data1Autocad_Course.csv)

(txt1, codepage is 1356, embedded labels, msq);

JavaCourse:

LOAD JavaStudentID,

StudentAddress,

StudentName

FROM

(D:QlikView2data2Java_Course.csv)

(txt1, codepage is 1455, embedded labels, msq);

VLSICourse:

LOAD VLSIStudentID,

StudentAddress,

StudentName

FROM

(D:QlikView3data3VLSI_Course.csv)

(txt1, codepage is 1576, embedded labels, msq);

In the above data, we have altered the column name “StudentID”  to “AutocadStudentID”, “JavaStudentID” and “VLSIStudentID”   for resolving the circular references.

Related Article: Optimization Techniques in Qlikview

The following screenshots will explain circular references in-detail

In the below screenshot, we can see the circular reference among three tables: 

By renaming city field to fanclub_city we fix the circular reference

Conclusion

These were some of the ways to avoid the circular reference condition that may arise during data analysis using the QlikView BI tool for making better business decisions. Hope, this article would have helped you understand the concept.  

If you have any doubt, please leave the query in the comment box below.