Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone!
I have an initial table that looks like this
column1 | column2 |
a | aa |
b | bb |
c | cc |
d | dd |
e | ee |
f | ff |
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
column1 | column3 |
a | 1 |
b | 2 |
c | 3 |
column2 | column3 |
dd | 4 |
ee | 5 |
ff | 6 |
So I have to somehow implement two separate left joins which would result in the following table:
column1 | column2 | column3 |
a | aa | 1 |
b | bb | 2 |
c | cc | 3 |
d | dd | 4 |
e | ee | 5 |
f | ff | 6 |
Is there any way to do it, because left join doesn't seem to work here
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:
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
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...
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:
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
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...