Two tables, resident loading and concatinate

[Evan0211]

I have two tables that I need to merge into one and I am having a difficult time getting it to merge with distinct IDs. 

Table 1

ID	Data
123	xyz
1234	yyy
111	aaa
145	bbb

table 2

ID

123

456

789

1234

111

145

Objective result

ID	Data
123	xyz
456	-
789	-
1234	yyy
111	aaa
145	bbb

Below is my load script, but I am getting duplicate ID values, example

ID	Data
123	xyz
123	-

_Numbers:
LOAD distinct 
"ID",
"Number" as DN2
Resident MGMT;
Concatenate Load distinct 
"ID"
Resident Master;

Is there a way on my concatenate load of the ID from Master to load only the ID values that do not exist in the MGMT table? 

[Oleg_Troyansky · Accepted solution]

Hi Evan,

Concatenation is not the best option in your case. It will simply unite both tables, with no process of matching key values. So, your Ids that exist in both tables, will end up in two rows - one with the data and one without.

In order to get the result that you wanted, you need to join the two tables. This way, matching rows will get consolidated into a single row, and not matching rows will get added with the missing values padded with NULL values. The syntax is similar, only with JOIN:

_Numbers:
LOAD distinct
"ID",
"Number" as DN2
Resident MGMT;
JOIN (_Numbers)

Load distinct
"ID"
Resident Master;