{"id":37061,"date":"2018-10-03T10:02:29","date_gmt":"2018-10-03T04:32:29","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=37061"},"modified":"2021-12-07T11:24:13","modified_gmt":"2021-12-07T05:54:13","slug":"qlikview-circular-reference","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/qlikview-circular-reference\/","title":{"rendered":"QlikView Circular Reference – Loosely Coupled Tables in QlikView"},"content":{"rendered":"

After studying QlikView Master Calendar<\/a><\/strong>. Here, we are going to study QlikView Circular Reference, in which we cover 3 major ways for resolving circular reference in Qlikview. Moreover, we will learn QlikView loosely coupled tables.<\/p>\n

QlikView Circular References can encounter during the time of data analysis. This occurrence resembles in some ways with that of synthetic key\u2019s scenario. Although the cause and cure to a circular reference are different from that of a synthetic key.<\/span><\/span><\/p>\n

So, let’s start QlikView Circular Reference Tutorial.<\/p>\n

What is Circular Reference in QlikView?<\/h3>\n

QlikView Circular References are loops that generate in QlikView\u2019s data models in a situation when three or more tables can associate with each other through two or more ways or paths. <\/span><\/span><\/p>\n

As we already know, that QlikView supports an associative data model which automatically links the tables having one or more fields in common. In the case of a circular reference, there are two or more than two common fields between three or more tables which creates an ambiguity in the logical structure and interpretation of data along with disrupting the data model. <\/span><\/span><\/p>\n

Formation of a circular loop or reference must avoid as much as possible because they degrade the performance by creating a logically distorted data structure. <\/span><\/span><\/p>\n

When a data with QlikView circular reference is viewed in a\u00a0table, it gives incorrect and errored information. However, QlikView software detects the presence of a circular reference during script execution and prevents the original data from being corrupt.<\/span><\/span><\/p>\n

Do you know the Properties of Documents in QlikView?<\/a><\/strong><\/p>\n

Let us consider a scenario where a QlikView circular reference creates. We load three tables into QlikView\u2019s memory.<\/span><\/span><\/p>\n

Table 1 – SalesRecords<\/strong><\/p>\n

Product_Id,Product_Line,Product_category,Product_quantity,Product_cost,Country<\/b><\/p>\n

pr1,Sporting Goods,Winter Sports & Activities,34,18000,USA<\/p>\n

pr2,”Food, Beverages & Tobacco”,Fruits & Vegetables,76,7000,USA<\/p>\n

pr3,Apparel & Accessories,Uniforms,59,20000,India<\/p>\n

pr4,Sporting Goods,Rugby,25,8458,Canada<\/p>\n

pr5,Health & Beauty,Body Lotion,44,5500,USA<\/p>\n

pr6,Arts & Entertainment,Musical Instruments,20,25000,Germany<\/p>\n

pr7,Arts & Entertainment,Orchestra Accessories,15,6700,Canada<\/p>\n

pr8,Arts & Entertainment,Crafting Materials,67,4200,India<\/p>\n

pr9,Hardware,Power Tool Batteries,74,3600,UK<\/p>\n

pr10,Home & Garden,Bath Caddies,66,2000,Germany<\/p>\n

pr11,”Food, Beverages & Tobacco”,Frozen Vegetables,56,5800,USA<\/p>\n

pr12,Home & Garden,Power Equipment,87,6300,UK<\/p>\n

pr13,Home & Garden,Water hose,42,7465,UK<\/p>\n

pr14,Health & Beauty,Aloe vera Gel,46,4852,USA<\/p>\n

pr15,Health & Beauty,Face pack,52,5673,India<\/p>\n

pr16,”Food, Beverages & Tobacco”,Cereals,49,6480,Canada<\/p>\n

pr17,”Food, Beverages & Tobacco”,Fruit juices,58,4900,Australia<\/p>\n

pr18,Sporting Goods,Football,40,5250,USA<\/p>\n

Table 2 – CountryCIty<\/strong><\/p>\n

Country, City<\/b><\/p>\n

USA, New York<\/p>\n

USA, New York<\/p>\n

India, Mumbai<\/p>\n

Canada, Ontario<\/p>\n

USA, San Fransisco<\/p>\n

Germany, Berlin<\/p>\n

Canada, Toronto<\/p>\n

India, Bangalore<\/p>\n

UK, London<\/p>\n

Germany, Berlin<\/p>\n

USA, Denver<\/p>\n

UK, Glasgow<\/p>\n

UK, Manchester<\/p>\n

USA, Dallas<\/p>\n

India, Delhi<\/p>\n

Canada, Montreal<\/p>\n

Australia, Melbourne<\/p>\n

USA, Michigan<\/p>\n

Also, See QlikView Star Schema<\/a><\/strong><\/p>\n

Table 3 – Product line & City<\/strong><\/p>\n

Product_Line, City<\/b><\/p>\n

Sporting Goods, New York<\/p>\n

Food & Beverages,\u00a0New York<\/p>\n

Apparel & Accessories, Mumbai<\/p>\n

Sporting Goods, Ontario<\/p>\n

Health & Beauty, San Fransisco<\/p>\n

Arts & Entertainment,Berlin<\/p>\n

Arts & Entertainment,Toronto<\/p>\n

Arts & Entertainment, Bangalore<\/p>\n

Hardware, London<\/p>\n

Home & Garden, Berlin<\/p>\n

Food & Beverages, Denver<\/p>\n

Home & Garden, Glasgow<\/p>\n

Home & Garden, Manchester<\/p>\n

Health & Beauty, Dallas<\/p>\n

Health & Beauty, Delhi<\/p>\n

Food & Beverages, Montreal<\/p>\n

Food & Beverages, Melbourne<\/p>\n

Sporting Goods, Michigan<\/p>\n

Now if you pay attention to the field heading of table 2 and 3, you will see that they have the field \u2018City\u2019 in common. This will cause a circular loop. Next, we will load these files into a script.\u00a0<\/strong><\/span><\/span><\/p>\n

Sales:\r\nLOAD Product_Id,\r\n     Product_Line,\r\n     Product_category,\r\n     Product_quantity,\r\n     Product_cost,\r\n     Country\r\nFROM\r\n[C:\\Users\\HP\\Desktop\\Dataflair\\Articles\\SalesRecord.csv]\r\n(txt, codepage is 1252, embedded labels, delimiter is ',', msq);\r\n\r\nCountryCity:\r\nLOAD Country,\r\n     City\r\nFROM\r\n[C:\\Users\\HP\\Desktop\\Dataflair\\countrycity.csv]\r\n(txt, codepage is 1252, embedded labels, delimiter is ',', msq);\r\n\r\nProducttypeCity:\r\nLOAD Product_Line,\r\n     City\r\nFROM\r\n[C:\\Users\\HP\\Desktop\\Dataflair\\productlinecity.csv]\r\n(txt, codepage is 1252, embedded labels, delimiter is ',', msq);<\/pre>\n
\"QlikView<\/a>
Image.1 QlikView Circular Reference Tutorial<\/p><\/div>\n
Follow this link to know How to Make – Bar, Pie & Text Box<\/a><\/strong><\/p>\n
As soon as you load the script and save it, you will get an error message warning you of the loop detection and comprehension ambiguities caused by the same.<\/span><\/span><\/p>\n
\"<\/a>
Image.2 QlikView Circular Reference Tutorial<\/p><\/div>\n
The table structure, invoked by CTRL+T, will look like that shown in the screenshot attached below. This is the table structure when tables are caught in a loop. There are three different pathways between these three tables, which are,<\/span><\/span><\/p>\n