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.
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 |
There are 3 major ways by which we can resolve the condition of the circular reference and they are as follows-
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.
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.
In this method, you can simply remove the matching column name from the table that is causing the circular reference to occur.
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.
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 |
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:
LOAD StudentID
StudentQualification
StudentName
FROM
(D:QlikView1data1Autocad_Course.csv)
(txt1, codepage is 1356, embedded labels, msq);
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 |
LOAD AutocadStudentID,
StudentQualification,
StudentName
FROM
(D:QlikView1data1Autocad_Course.csv)
(txt1, codepage is 1356, embedded labels, msq);
LOAD JavaStudentID,
StudentAddress,
StudentName
FROM
(D:QlikView2data2Java_Course.csv)
(txt1, codepage is 1455, embedded labels, msq);
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.
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:
Name | Dates | |
---|---|---|
QlikView Training | Dec 14 to Dec 29 | View Details |
QlikView Training | Dec 17 to Jan 01 | View Details |
QlikView Training | Dec 21 to Jan 05 | View Details |
QlikView Training | Dec 24 to Jan 08 | View Details |
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.