Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

joining two table who have three field in common

Hi I want to join two tables that have three tables in common

table1:

Load a, b, c, d, e,f

from table1;

and the second table is :

table2:

Load a, b, c, , j, l

from table2;

to join this two tables based on table1.a=table2.a and table1.b=table2.b and table1.c=table2.c

should I use simply a join between them like this

Load a, b, c, d, e,f

from table1;

join

Load a, b, c, , j, l

from table2;

or what do you suggest ?

7 Replies
Anonymous
Not applicable
Author

Depends on what data output you want. Single table with all the records from both tables?

table1.a=table2.a and table1.b=table2.b and table1.c=table2.c

This will actually give you an intersection dataset. So you'll have to use "inner join" in Qlik load script.

rittermd
Master
Master

If you want all three tables to join by the 3 common fields then you need to build a key in each of them which contains those three values and comment out the original fields or give them unique names so that they no longer associate.

Anonymous
Not applicable
Author

Yeah that seems like a good idea. Instead of a new key, you could concatenate these 3 fields in each of the tables and define a new column Concat_KEY.

This will ensure you are joining using 3 columns. But again, you need to use left/right/inner join based on the sort of output you are expecting.

Benazir

Anonymous
Not applicable
Author

actually I want to join the two tables by the three common fields that have the same names

Anonymous
Not applicable
Author

If your three fields have the same names/aliases, then Qlik will automatically do the join for you depending on the data in these fields.

If you explicitly want to define

table1.a=table2.a and table1.b=table2.b and table1.c=table2.c

then you can try concatenate the three fields as a single column and give aliases to your other columns.

Mark_Little
Luminary
Luminary

HI Khouloud,

The tables will join without the need of a concatenated key, but how you join them will really depend on how you want you your new table to look afterward?

For example if the first table is youe main table and you are only interested in seeing the data from table 2 and 3 where the values in A,B,C match you would

Just Load table 1 and left Join Table 2 and then Left Join Table 3.

If you give an example of the data in each of yout tables and an example how ou want your final data table to look and then we could advised how you can get there.

Mark

YoussefBelloum
Champion
Champion

Hi,

Yes, Just choose the right type of join you need (LEFT/RIGHT JOIN ETC)

and doing this, will be ok:

Table1:

Load a, b, c, d, e,f

from table1;

join //OR LEFT JOIN or RIGHT JOIN

Table2:

Load a, b, c, j, l

from table2;

you will have Table1 with this structure:

a, b, c, d, e,f,j,l