How to spot loops from the internal table viewer?

[#1]

I am following a guide where there is an example of a loop. According to the author it is obvious that the line dotted lines helps you identify the problem:

"

"

In this case the address field is the problem. Yes it is clear that they are there twice, but how does these dotted lines help you identify that?

[rustyfishbones]

You have a synthetic table, which means you have more than 1 field that your are trying to join

In fact the problem here is not just Address, it's also City, Region, PostalCode and or Country

Try creating a Link Table

A Link Table is used when you are trying to Join tables with more than 1 Field

HTH

Regards

Alan

[Gysbert_Wassenaar]

The Orders table is the center of the loop problem. All key fields that link this table to other tables are connected with dotted lines. But the line between the OrderID fields is not helpful to identify the loop.

A loop exists if there are two or more routes to arrive at a table. The problem here is that Customers and Employees are linked on the composite key (Address, City, Region, PostalCode, Country). So there's a direct link between Customers and Employees. But there's also a route via the Orders Table. So there's a loop.

To fix this you can rename fields in the Customers or Employee tables so those two tables are no longer directly linked, but only to the Orders table.

[#4]

Yes and sorry for not being specific: Address fields including Address, City, Region, PostalCode and Country. The guide ask me to exclude all of these. And yes there is a comment about synthetic keys I got that, but the dotted lines does not tell me that right!?

If I spot the Ssyn 1 table and follow the grey lines, I can see that the synthetic key does not make sense.

[Anonymous]

Mikael

To answer your question:

In this case the address field is the problem. Yes it is clear that they are there twice, but how does these dotted lines help you identify that?

It means that Qlikview has noticed a circular reference and marked your table Orders as loosely coupled. In the Table Viewer hover over the table and it will tell you it is loosely coupled.  All lines denoting joins to / from a loosely coupled table are dotted.

Hence [assuming it was not you who manually marked a table as loosely coupled] if you have dotted lines in the table viewer then ipso facto you must have a loop [aka circular reference].

HIC wrote an illuminating blog on circular references earlier this year.

Best Regards,,     Bill

[#6]

Gysbert and Bill: Thx for trying to explain further, I think I got it kind of. I think I might expected to much of the "dotted lines", they tell me there is problem and that is all. When I fix the issue I get a table where the Syn-key is gone and the dotted line are no more dotted. I mean the tables are still suppose to be linked that way, the only thing that gave me hint was actually the Syn-key from the to tables.

At the moment I am just doing testing and learning for personal use, but one day I might need a Syn-key to actually be there.    

[#7]

Syn-Key are bad to the data model only it consumes more RAM to perform the calculations. So we must avoid the Syn-keys and we server our purpose by creating the Composite key if the 2 two tables must be join more than one key.

[#8]

Syn-key vs. Composite key, plz!? I thought it was kind of the same.

[#9]

Can you aware of the Composite Key in DWH concepts? Composite Key is just concatenation of more that 1 field

In Sql, KEYFEILD1 || '-' || KEYFIELD2 AS KEY_COMPOSTITE_KEY

In Qlikview:  KEYFEILD1 & '-' & KEYFIELD2 AS KEY_COMPOSTITE_KEY

In Qlikview if two tables linked more than one key created Syn-Key. To avoid Syn-Key we are making Syn-Key by using Auto Number function.

For Example:  AutoNumber(KEYFEILD1 & '-' & KEYFIELD2) AS KEY_FIELD

The Purpose of Automunber is to avoid the very big strings in the KET fields and it creates a unique no for each combination values.

[#10]

Sorry I am not strong with the basic concepts yet, so I don't understand the difference between || '-' || and & '-' &  ? Does it do the same, just different languages?

I know what a (single/standard) key is and thought that there was only two types of keys, the key with one field and keys with two or more fields called either syn/comp/con-keys?

I do understand that you can merge two fields and make it a single key, thx.