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: 
Andrey_Kosarev
Contributor II
Contributor II

Join three tables on different fields

Hi everyone!

I have an initial table that looks like this

column1column2
aaa
bbb
ccc
ddd
eee
fff

 

I need to join a third column to this table. The problem is this column is contained in two different tables that can only be joined separately on different keys. 

The tables look like this

column1column3
a1
b2
c3

 

column2column3
dd4
ee5
ff6

 

So I have to somehow implement two separate left joins which would result in the following table:

column1column2column3
aaa1
bbb2
ccc3
ddd4
eee5
fff6

 

Is there any way to do it, because left join doesn't seem to work here

1 Solution

Accepted Solutions
QlikTom
Employee
Employee

With the date provided I was able to author this script:

 

//load first table
Table1:
Load * inline [
column1,column2
a,aa
b,bb
c,cc
d,dd
e,ee
f,ff
];

//left join table2 into table1, the join will happen on column1
Left Join (Table1)
Table2:
Load * inline [
column1,column3
a,1
b,2
c,3
];

//left join table3 into tabl1 (**Which at this point in the script, contains *column3* from table2**) on BOTH column2 AND column3
Left Join (Table1)
Table3:
Load * inline [
column2,column3
dd,4
ee,5
ff,6
];

 

 

Which results in:

QlikTom_0-1593706992842.png

This is the correct result based on your logic and values. 

It may be that you are looking to do an outer join 

 

//load first table
Table1:
Load * inline [
column1,column2
a,aa
b,bb
c,cc
d,dd
e,ee
f,ff
];

//outer join table2 into table1, the join will happen on column1
Join (Table1)
Table2:
Load * inline [
column1,column3
a,1
b,2
c,3
];

//outer join table3 into tabl1 (**Which at this point in the script, contains *column3* from table2**) on BOTH column2 AND column3
Join (Table1)
Table3:
Load * inline [
column2,column3
dd,4
ee,5
ff,6
];

 

 

*note the table as a few rows longer, but cut off by the preview

QlikTom_1-1593707157765.png

 

It may also be that you are looking for some combination of join logic. Just remember that a as the script is running, top -> bottom , the statements are processed in order. So after the joining of Table2 to Table1, all fields from Table2 are now a part of Table1 and Table2 no longer exists by the time you get to the Table3 load statement .

Hopefully this helps. 

Help article on Joins:
https://help.qlik.com/en-US/sense/latest/Subsystems/Hub/Content/Sense_Hub/Scripting/combine-tables-j...

View solution in original post

1 Reply
QlikTom
Employee
Employee

With the date provided I was able to author this script:

 

//load first table
Table1:
Load * inline [
column1,column2
a,aa
b,bb
c,cc
d,dd
e,ee
f,ff
];

//left join table2 into table1, the join will happen on column1
Left Join (Table1)
Table2:
Load * inline [
column1,column3
a,1
b,2
c,3
];

//left join table3 into tabl1 (**Which at this point in the script, contains *column3* from table2**) on BOTH column2 AND column3
Left Join (Table1)
Table3:
Load * inline [
column2,column3
dd,4
ee,5
ff,6
];

 

 

Which results in:

QlikTom_0-1593706992842.png

This is the correct result based on your logic and values. 

It may be that you are looking to do an outer join 

 

//load first table
Table1:
Load * inline [
column1,column2
a,aa
b,bb
c,cc
d,dd
e,ee
f,ff
];

//outer join table2 into table1, the join will happen on column1
Join (Table1)
Table2:
Load * inline [
column1,column3
a,1
b,2
c,3
];

//outer join table3 into tabl1 (**Which at this point in the script, contains *column3* from table2**) on BOTH column2 AND column3
Join (Table1)
Table3:
Load * inline [
column2,column3
dd,4
ee,5
ff,6
];

 

 

*note the table as a few rows longer, but cut off by the preview

QlikTom_1-1593707157765.png

 

It may also be that you are looking for some combination of join logic. Just remember that a as the script is running, top -> bottom , the statements are processed in order. So after the joining of Table2 to Table1, all fields from Table2 are now a part of Table1 and Table2 no longer exists by the time you get to the Table3 load statement .

Hopefully this helps. 

Help article on Joins:
https://help.qlik.com/en-US/sense/latest/Subsystems/Hub/Content/Sense_Hub/Scripting/combine-tables-j...