Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
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:
@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);
Thank you Taufiq
This seems to work
Thank you Kush, but this does not work. Method above seems to do the trick though.