Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
PC
Contributor II
Contributor II

Outer Join on multple columns with some null values

Hi all

 

I am trying to join two tables in the data model based on two columns that are present in both tables, ID_1 and ID_2. In table 1 the column ID_1 has no null values, but can have null values in ID_2 and vice versa for table 2.

A non null set may be present in one table but one is null in the other table (e.g. ID_1 and ID_2 not null for a record in table 2 but ID_2 is null in table 1). If I use regular OUTER JOIN I would get this record as a double up, I don't want that to happen.

Is there a simple way of getting the correct output?

Cheers

Example.png

 
 
 

 

 

Labels (3)
1 Solution

Accepted Solutions
Taoufiq_Zarra
Master II
Master II

@PC  One solution using Lookup

 

Table1:

load * inline [
ID_1,ID_2
101,203
102,
103,209
104,210
105,
106,202
107,
108,
109,
110,206
];

Table2:

load ID_1,ID_2 as ID_2_tmp inline [
ID_1,ID_2
109,201
,202
101,203
107,204
,205
,206
,207
,208
103,209
,210
];

Tmp:
noconcatenate
load ID_1,if(isnull(lookup('ID_2_tmp','ID_1',ID_1,'Table2')),ID_2,lookup('ID_2_tmp','ID_1',ID_1,'Table2')) as ID_2 resident Table1;

load ID_2_tmp as ID_2,if(isnull(lookup('ID_1','ID_2',ID_2_tmp,'Table1')),ID_1,lookup('ID_1','ID_2',ID_2_tmp,'Table1')) as ID_1 resident Table2;


drop tables Table1,Table2;


Final:
noconcatenate
load distinct ID_1,ID_2 resident Tmp;

drop tables Tmp;

 

output:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

4 Replies
Taoufiq_Zarra
Master II
Master II

@PC  One solution using Lookup

 

Table1:

load * inline [
ID_1,ID_2
101,203
102,
103,209
104,210
105,
106,202
107,
108,
109,
110,206
];

Table2:

load ID_1,ID_2 as ID_2_tmp inline [
ID_1,ID_2
109,201
,202
101,203
107,204
,205
,206
,207
,208
103,209
,210
];

Tmp:
noconcatenate
load ID_1,if(isnull(lookup('ID_2_tmp','ID_1',ID_1,'Table2')),ID_2,lookup('ID_2_tmp','ID_1',ID_1,'Table2')) as ID_2 resident Table1;

load ID_2_tmp as ID_2,if(isnull(lookup('ID_1','ID_2',ID_2_tmp,'Table1')),ID_1,lookup('ID_1','ID_2',ID_2_tmp,'Table1')) as ID_1 resident Table2;


drop tables Table1,Table2;


Final:
noconcatenate
load distinct ID_1,ID_2 resident Tmp;

drop tables Tmp;

 

output:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

Kush
MVP
MVP

@PC  try below

Table1:
load * inline [
ID_1,ID_2
101,203
102,
103,209
104,210
105,
106,202
107,
108,
109,
110,206
];

Join

load * inline [
ID_1,ID_2
109,201
,202
101,203
107,204
,205
,206
,207
,208
103,209
,210
] where not Exists(ID_2);

Screenshot 2020-10-05 122358.png

PC
Contributor II
Contributor II
Author

Thank you Taufiq

This seems to work

PC
Contributor II
Contributor II
Author

Thank you Kush, but this does not work. Method above seems to do the trick though.