Join tables with if condition

[olenroko]

Hi everyone,

I have two tables:

Table A:

id_level_1	id_level_2	Info
64356		hdhd
64566		gdfg
27457	27457	shdghsy
37567	37567	dsdfg
	57895	sdf
	95789	gadg
656334		agfvsadgv

Table B:

id	name
64356	Adrian,Funke
64566	Mike, Scholz
27457	Alex, Kinder
37567	Andre, Wolf
57895	Lisa, Lunwig
95789	Liukas, Linder

I need to join the second table to the first table on 'id' and 'id_level_1'. If there no 'id' that matches 'id_level_1', then join on 'id_level_2'.

My code:

Table_1:
LOAD id_level_1,
id_level_2,
Info
FROM
Table A;

Table_2:
LOAD
id_level_2
FROM
Table A;

left join(Table_1)
load distinct
id as id_level_1,
name
FROM
Table B;

if isnull(name) then
left join(Table_2)
load distinct
id as id_level_2,
name
FROM
Table B;
ENDIF;

The problem is that the date from the second join is just added to the end of the table and the right places are empty.

So the expected result would be look like this:

id_level_1	id_level_2	Info	name
27457	27457	shdghsy	Alex, Kinder
37567	37567	dsdfg	Andre, Wolf
64356		hdhd	Adrian,Funke
64566		gdfg	Mike, Scholz
656334		agfvsadgv
	57895	sdf	Lisa, Lunwig
	95789	gadg	Liukas, Linder

I would be really grateful for your help!

[marcus_sommer · Accepted solution]

I suggest to use a mapping-approach instead of joining the data, for example like:

m_ mapping load id, name from B;

A: load *, applymap('m', id_level_1, applymap('m', id_level_2, 'no name')) as name
from A;

- Marcus

[sidhiq91]

@olenroko  Please follow the below Script to get the desired output, also I have attached the screenshot of the output. If it has resolved your issue please like and accept it as your solution.

NoConcatenate
TableA:
Load *,
Coalesce(EmptyIsNull(id_level_1),EmptyIsNull(id_level_2)) as ID;

load * inline [
id_level_1, id_level_2, Info
64356,,hdhd
64566,,gdfg
27457,27457,shdghsy
37567,37567,dsdfg
,57895,sdf
,95789,gadg
656334,,agfvsadgv];

left join (TableA)
Load * Inline [
ID, name
64356, Adrian Funke
64566, Mike Scholz
27457, Alex Kinder
37567, Andre Wolf
57895, Lisa Lunwig
95789, Liukas Linder

];

Exit Script;

[olenroko]

Works great! Thank you!