Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rochdkhouloud
New Contributor

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
benazirkhan
Contributor II

Re: joining two table who have three field in common

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
Honored Contributor

Re: joining two table who have three field in common

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.

benazirkhan
Contributor II

Re: joining two table who have three field in common

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

rochdkhouloud
New Contributor

Re: joining two table who have three field in common

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

benazirkhan
Contributor II

Re: joining two table who have three field in common

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.

mark6505
Valued Contributor III

Re: joining two table who have three field in common

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
Esteemed Contributor

Re: joining two table who have three field in common

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

Community Browser