connecting tables with same IDs

[#1]

hi

I have 3 tables. all with the same primary key. how to i connect the first one with the second and connect the second with the third BUT NOT THE THIRD WITH THE FIRST. Qlikview connect all the 3 by default....

Example:

table1: primary key>>F1

table2: primary key>>F1

table3: primary key>>F1

I dont want all the tables to connect like this. just want "spock" to connect to "flower" and "flower" to "lyon". is it possible?

[#2]

If Qlikview makes a relation that you don't like to have in your model, this can often be resolved by aliassing column.

Like this sample code

LOAD id as myNotRelatedID INLINE [ ]

Does that answer your question?

If not, could you make a small example app with the 3 tables in it (can be fake data of course...)

[swuehl]

Yes, QV will link all three tables by default if the field names are the same.

But you will only get a 'working' link if you also have matching values in each of the tables.

So if your first table and your third table have only distinct values, they won't really link. If your second table shows values that are also part of the first resp. third table field, the second table will be linked to the first and the third.

Not sure if I make myself clear and even more unsure if I understand what your issue is. Could you post some sample lines and your expected outcome, or describe what your issue is in detail?`

You could also duplicate the key field in the second table and rename it. Then rename the key field in the third table to the same name, so you only get links 1<-->2 and 2<-->3

[#4]

Hi swuehl, i understand your point of view. but is it normal if a syn table is created if i add a third field in the tables?

like this:

[#5]

hi Roberto, i am not trying to change the field's names. but is it possible using QUALIFY and UNQUALIFY?

[#6]

I wasn't saying that you are trying to change column names, but I think that is what you need to make sure Qlikview makes the right relations (see also swuehls answer). Qlikview just checks the column names of the loaded tables and draws a relation when the column name is similar.

So if you don't want spock to be connected to lyon, you should (in your load script) make sure that the column name F1 does not exist anymore for that table. That can be done by

load fieldname as other_fieldname

Synthetic keys (the other issue you're referring to) is when you have 2 tables in your model where Qlikview can draw 2 relations... This should be avoided. So in your case there is a relation between F1 and F2 in both tables.

A way to fix is: make a new column in both tables, like

- load F1 + F2 as F1F2Key

- removing the fields F2 and F1 from these tables (or rename them with an alias)

- resulting in 1 relation between the tables over the F1F2 key field...

Does this help?

If not, please upload a sample app, then I'll see what I can change to make it even more clear.

[swuehl]

Technically spoken, it is normal that a syn key table is created using QV with tables linked by more than one common field.

The question is if the syn key link is appropriate for your setting, to answer this you need to tell us more about your data and what your requirements are.

And yes, QUALIFY and UNQUALIFY might be a solution. But this is similar to renaming fields in my opinion.

[#8]

Here are my tables

but the problem is that i do not know if its good when the syn tables are created:

i mean qlikview has the habit of creating a syn table for each set of common field. syn with fields<A, B> and another syn with fields<A B C D>. is it normal?

[#9]

Maybe it's too late, but here it's my solution:

Table1:

F1

F1 as F2

Table2:

F1

Table3:

F1 as F2

With this solution you have 2 keys with the same info but you haven't Sync table and relationships are as you want.

Regards,

Jose