Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

There are two Swedish car brands, Volvo and SAAB. Or, at least, there used to be... SAAB was made in Trollhättan and Volvo was – and still is – made in Gothenburg.

Two fictive friends – Albert and Herbert – live in Trollhättan and Gothenburg, respectively. Albert drives a Volvo and Herbert drives a SAAB.

If the above information is stored in a tabular form, you get the following three tables:

Tables.png

Logically, these tables form a circular reference: The first two tables are linked through City; the next two through Person; the last and the first through Car.

Further, the data forms an anomaly: Volvo implies Gothenburg; Gothenburg implies Herbert; and Herbert implies SAAB. Hence, Volvo implies SAAB – which doesn’t make sense. This means that you have ambiguous results from the logical inference - different results depending on whether you evaluate clockwise or counterclockwise.

If you load these tables into QlikView, the circular reference will be identified and you will get the following data model:

Circular reference.png

To avoid ambiguous results, QlikView marks one of the tables as “loosely coupled”, which means that the logical inference cannot propagate through this table. In the document properties you can decide which table to use as the loosely coupled table. You will get different results from the logical inference depending on which you choose.

So what did I do wrong? Why did I get a circular reference?

It is not always obvious why they occur, but when I encounter circular references I always look for fields that are used in several different roles at the same time. One obvious example is if you have a table listing external organizations and this table is used in several roles: as Customers, as Suppliers and as Shippers. If you load the table only once and link to all three foreign keys, you will most likely get a circular reference. You need to break the circular reference and the solution is of course to load the table several times, once for each role.

In the above data model you have a similar case. You can think of Car as “Car produced in the city” or “Car that our friend drives”. And you can think of City as “City where car is produced” or “City where our friend lives”. Again, you should break the circular reference by loading a table twice. One possible solution is the following:

Tables2.png

In real life circular references are not as obvious as this one. I once encountered a data model with many tables where I at first could not figure out what to do, but after some analyzing, the problem boiled down to the interaction between three fields: Customers, Machines and Devices. A customer had bought one or several machines; a device could be connected to some of the machine types – but not to all; and a customer had bought some devices. Hence, the device field could have two roles: Devices that the customer actually had bought; and devices that would fit the machine that the customer had bought, i.e. devices that the customer potentially could buy. Two roles. The solution was to load the device table twice using different names.

Bottom line: Avoid circular references. But you probably already knew that…

HIC

Further reading on Qlik data modelling:

To Join or not to Join

Synthetic Keys

Fan traps and Chasm traps

18 Comments
saurabh_pandit
Contributor

Thanks HIC for such a simple and clear explanation.

It has helped me solved one of my queries.

http://community.qlik.com/message/277728#277728

Thanks a ton !

0 Likes
2,519 Views
Partner
Partner

Thanks HIC,

This thread solved my question and idea about how to solve the circular references.

Keep in posting your deas.

Thanks,

Kalyan

0 Likes
2,519 Views

Henric

Great post, but please may I ask for clarification on one bit of it.

You say in your blog post:

  • To avoid ambiguous results, QlikView marks one of the tables as “loosely coupled”, which means that the logical inference cannot propagate through this table.


Sorry if I am being stupid, but  could you explain what you mean by the the logical inference cannot propagate through this table bit ?



Best Regards,     Bill


0 Likes
2,519 Views

No, you're not being stupid. The post assumes that the reader knows what the Logical Inference engine does.

The Logical Inference engine is the core of QlikView. It evaluates which field values are possible, given the selection. Basically it first evaluates which records are possible, and then the result of the evaluation "propagates" into the next table via the possible values of the key field, and then the next table is evaluated. It is this propagation that is disabled by the loosely coupled table.

Read more about Logical Inference under http://community.qlik.com/blogs/qlikviewdesignblog/2013/07/15/logical-inference-and-aggregations

HIC

2,519 Views

HIC

Many thanks for the url to the Logical Inference blog post, I shall read & digest it.

Best Regards,     Bill

0 Likes
2,519 Views
haty
New Contributor III

This is very clear and helpful, thanks

0 Likes
2,519 Views
geetaalhan
Contributor

Thanks for sharing .

Very helpful.

0 Likes
2,519 Views
Not applicable

Hello,

Another solution couldn't be to rename the two city fields?

Such as:

Person-car:

Load

Car,

Person

from...

City-car:

Load

Car,

City as [City where car is produced]

from...

Person-city:

Person,

City as [City where our friend lives]

from...

Or using qualify and unqualify statements?

Thx!

2,519 Views
Not applicable

model.jpg

Dear HIC,

as usually your post are more then IMPRESSIVE!!! but for this one I would like to propose different approach... lets keep to my BI Bible written by two persons:  Ralph Kimball for Realtional world... and Henric Cronstrom for Acosiative world... and let's stick to idea of modeling world that always aim to star schema for later efficiency of analitics. This is my proposition of code to remodel loop situation into start schema design

///$tab source

[Person-Car_source]:
LOAD * INLINE [
    Person, Car
    Albert, Volvo
    Herbert, SAAB
];

[Person-City_source]:
LOAD * INLINE [
    Person, City
    Albert, Trollhattan
    Herbert, Gotheburg
];

[City-Car_source]:
LOAD * INLINE [
    Car, City
    Volvo, Gotheburg
    SAAB, Trollhattan
];
///$tab persons
tmp1:
load distinct
Person
Resident
[Person-Car_source];

Concatenate(tmp1)
load distinct
Person
Resident
[Person-City_source];

Dim_Person:
load Distinct Person,
RowNo() as PersonID
Resident tmp1;


map_Person:
mapping load Distinct Person,
RowNo() as PersonID
Resident tmp1;

drop Table tmp1;
///$tab cars
tmp1:
load distinct
Car
Resident
[Person-Car_source];

Concatenate(tmp1)
load distinct
Car
Resident
[City-Car_source];

Dim_Car:
load Distinct Car,
RowNo() as CarID
Resident tmp1;


map_Car:
mapping load Distinct Car,
RowNo() as CarID
Resident tmp1;

drop Table tmp1;


///$tab cities
tmp1:
load distinct
City
Resident
[Person-City_source];

Concatenate(tmp1)
load distinct
City
Resident
[City-Car_source];

Dim_City:
load Distinct City,
RowNo() as CityID
Resident tmp1;


map_City:
mapping load Distinct City,
RowNo() as CityID
Resident tmp1;

drop Table tmp1;

///$tab link table
[LinkingTable]:
LOAD
ApplyMap('map_Person', Person, 0) as PersonID,
ApplyMap('map_Car', Car, 0) as CarID,
'Person-Car' as Relation
Resident [Person-Car_source];

drop Table [Person-Car_source];

Concatenate
LOAD
ApplyMap('map_Person', Person, 0) as PersonID,
ApplyMap('map_City', City, 0) as CityID,
'Person-City' as Relation
Resident [Person-City_source];

drop Table [Person-City_source];

Concatenate
LOAD
ApplyMap('map_Car', Car, 0) as CarID,
ApplyMap('map_City', City, 0) as CityID,
'City-Car' as Relation
Resident [City-Car_source];

drop Table [City-Car_source];

kind regards

2,519 Views
sakamsureshbabu
Contributor

Thanks HCI

0 Likes
2,519 Views
Karim_Khan
Contributor III

Very Nice Post

0 Likes
2,519 Views
Not applicable

Nice post. It helped me a lot!

Best regards!

Gabrielle

0 Likes
2,519 Views
beck_bakytbek
Honored Contributor

Thanks a lot for your sharing

2,519 Views
wizardo
Contributor III

Hi,

i know its a bit late to the party but hope ill get an answer still,

my question is this:

is circular references  "content" based or  "schema" based?

what do i mean by that. let me use the tables in HIC post as example.

what if the cars in the "persons/cars" table ware not what they ware, lets say the ware

person        car

alebrt          ford

herbert        fiat

in this case when this table connects back to the "cars/cities" table it wont create a problem.

we will still see the same "Table Diagram" where all the tables are connected (A to B, B to C, C to A)

in this scenario, will qlikview complain about "loops found" or will qlikview just draw solid lines and wont say there is a problem.

hope i made myself clear.

im asking this because i have many examples of data-models where there seems to be many "circular references" but i do not get the "loops" message

Thanks

Daniel

0 Likes
2,519 Views
Partner
Partner

While not knowing for certain I'm pretty sure it is schema based.

Do you have an example of those data-models where there seems to be circular references but no loops?
Probably something in your data-model that you have overlooked.

0 Likes
2,519 Views

It's schema based.

The content can never change the fact that you have a logical inconsistency: Whatever content you have, you will always have two ways to make the logical evaluation: clockwise or counterclockwise. If you, as you suggest, use Ford and Fiat in one of the tables, a click on 'Herbert' would imply 'Gothenburg' if you evaluate counterclockwise, and an empty result set (no city possible) if you evaluate clockwise. This is still a logical contradiction.

HIC

2,519 Views
businessajit2
New Contributor III

Very useful article hic

Thanks a lot

0 Likes
2,519 Views
antwnina
New Contributor III

The renaming of the fields isn't a solution?

0 Likes
2,519 Views