topic What is Circular reference? How to avoid them? in QlikView

What is Circular reference? How to avoid them?

its_anandrjs — Mon, 30 May 2011 04:57:18 GMT

Hi All,

What are circular references and how to avoid them

Anand

What is Circular reference? How to avoid them?

Mon, 30 May 2011 05:04:06 GMT

Refer to qlik View reference manual..Pg No. 145.

What is Circular reference? How to avoid them?

its_anandrjs — Mon, 30 May 2011 08:13:08 GMT

Thanks of you. Its nice and helpful.

Anand

Regards

What is Circular reference? How to avoid them?

Thu, 09 Jun 2011 10:42:40 GMT

yes OK - but what if the home country and the car manufacturing

country are both important - i want to load the country table once and then join it to whereever i like/

What is Circular reference? How to avoid them?

Thu, 09 Jun 2011 15:29:22 GMT

Load the Country Table Twice but in second one call it country2:

What is Circular reference? How to avoid them?

Thu, 09 Jun 2011 17:15:44 GMT

you don't need a weather man to know to where the wind blows - it is not elegant - ther must be a way to load the data once.

What is Circular reference? How to avoid them?

Thu, 09 Jun 2011 18:34:23 GMT

I would suggest generating several key fields in the country table to tie to your various other tables.

i.e.

country:

load 'car_mfg'&country as car_mfg_country,

country

from country_table;

car_mfg:

load 'car_mfg_&country as car_mfg_county,

state,

city,

etc..

from car_mfg_table;

notice you aren't trying to tie the country field from the car_mfg_table to the country field from the country_table.

What is Circular reference? How to avoid them?

Fri, 10 Jun 2011 05:34:13 GMT

U missd the point - Country is a key in both the tables - Home & Carmaker (see p.146 in the manual).

In adition - lets take the situation one step forward - now lets consider that i have a big table of countries, with many columns, such as flag image, and population, area in square meters, UN symbol, continent, and many more.

the Q is - how to load the table once but use its data twice, without affecting eac other.

I believe it has to do with some design pattern such as using QVD or whatever.

Does QlickTech members are monitoring this forum?

Anybody?

What is Circular reference? How to avoid them?

Anonymous — Fri, 10 Jun 2011 05:35:47 GMT

What does your data model look like at the moment?

What is Circular reference? How to avoid them?

Fri, 10 Jun 2011 05:54:59 GMT

Thanks for responsing'

For the data model:

See page 146 in the manual and add one more table of countries.

For my Q:

I used to work few yesrs with microsoft SSAS and it was posible to load the countries table once and use its data as many times as you like, with the option to select CANDA as the Carmaker country and USA as the BuyerHome country.

What is Circular reference? How to avoid them?

Fri, 10 Jun 2011 08:20:44 GMT

One thing to remember is that QlikView compresses data so you don't have to worry about duplicate values or load the same table twice!

What is Circular reference? How to avoid them?

Fri, 10 Jun 2011 08:40:59 GMT

Remembering this is not relevant - let us load the data once and let it be compressed anyway, how does compression is related to this issue? i didn't get it!

Re: What is Circular reference? How to avoid them?

Fri, 10 Jun 2011 08:43:28 GMT

So you can load the same table twice to avoid circular reference? It's very trivial. First load the data from SQL and store it into QVD. Then load country data from thsi QVD twice.

edit:

QVD load is very fast. So in a way you are loading data only once from SQL to QVD.

What is Circular reference? How to avoid them?

Mon, 13 Jun 2011 09:19:31 GMT

Handling loops in datawarehouses is not as straightforward as you might think. Some OLAP tools create Cartesian products between all possible combinations of dimensions, resolving different subqueries for each combination. Although the simplicity of the datamodel seems appealing, you wont like the drop in performance and issues it brings (ambiguity in query construction).

So I can understand why Qlik chose to restrict loops, and as a consequence, I don't think it's possible to use a single country table the way you intended. On the bright side, the design modifications arnt that difficult. The only argument I can think of in favor of having one table, is if you want a single select field COUNTRY, and all data on the sheet to correspond to that. You can use field triggers to align home country and car manufacturing country to resolve that.

What is Circular reference? How to avoid them?

Mon, 13 Jun 2011 09:56:33 GMT

I dont want all the data on the sheet to correspond to a single select field COUNTRY.

I want to have multiple COUNTRY fields (e.g Home COUNTRY & Carmaker COUNTRY) but to load the COUNTRY table once, and link it twice.

What is Circular reference? How to avoid them?

Mon, 13 Jun 2011 10:20:21 GMT

What are you trying to accomplish by linking twice to the same table?

What is Circular reference? How to avoid them?

christian77 — Mon, 13 Jun 2011 10:36:05 GMT

Hi its.anandrjs:

Reloading complex, weirdly related and large amount of data, which means, creating the associative model, becomes more than once an art.

Since theory is practicably impracticable, please send us an example and your model will be tight satisfactorily by any one of the hundreds of bright brains in this flourish community.

What is Circular reference? How to avoid them?

Mon, 13 Jun 2011 11:03:16 GMT

The data is not "Wierdly related" - see page 146 in the manual - i want to see the sales of French cars by people who live in Canda - this is it, simply stated - what is the design practice for loading the countries once, and link it twice?