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. 

QlikView Circular Reference

 

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. 

QlikView Circular Reference

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.

MindMajix YouTube Channel

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: 

QlikView Circular Reference - circular references in-detail

By renaming city field to fanclub_city we fix the circular reference

QlikView Circular Reference - 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.

Explore QlikView Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

Job Support Program

Online Work Support for your on-job roles.

jobservice

Our work-support plans provide precise options as per your project tasks. Whether you are a newbie or an experienced professional seeking assistance in completing project tasks, we are here with the following plans to meet your custom needs:

  • Pay Per Hour
  • Pay Per Week
  • Monthly
Learn MoreGet Job Support
Course Schedule
NameDates
QlikView TrainingDec 14 to Dec 29View Details
QlikView TrainingDec 17 to Jan 01View Details
QlikView TrainingDec 21 to Jan 05View Details
QlikView TrainingDec 24 to Jan 08View Details
Last updated: 03 Apr 2023
About Author

Pooja Mishra is an enthusiastic content writer working at Mindmajix.com. She writes articles on the trending IT-related topics, including Big Data, Business Intelligence, Cloud computing, AI & Machine learning, and so on. Her way of writing is easy to understand and informative at the same time. You can reach her on LinkedIn & Twitter.

read less