Joining 3 Tables - getting the unique combination of both name and country

[nickmarlborough]

Hi, 

Summary - i have 3 tables. 

Table 1 (Country/Name) = a file showing all the names and their respective country mappings.

Country ID	Name
Japan	Nick
UK	Nick
Germany	Nick
UK	James
USA	James

Table 2 (Region Specific) = a file showing all the names and their respective country mappings BUT only countries tied to a specific region (same format as above, but limited scope) - you may also get some names & countries in this file that do not appear in Table 1, but there is definite overlap, i.e. Japan - Nick may appear in this file

Table 3 (Full List) = shows a full list of names however does not give a country ID, so we can consider any name not mapped to a country 'unmapped' 

I am trying to get a unique list of every mapping of country ID to name PLUS any name not mapped, and for those to appear as 'Not Mapped' in the Country ID column. 

[Name Table]:
load Distinct
[Country ID],
[Country/Name.Name] as [Name]
Resident [Country/Name] ;

Concatenate

load Distinct
[Country ID],
[Region Specific.Name] as [Name]
Resident [Region Specific] ;

Concatenate

load Distinct
[Full List.Name] as [Name]
Resident [Full List] ;

[LRuCelver]

Table1:
NoConcatenate Load * Inline [
    Country ID,	Name
    Japan,		Nick
    UK,			Nick
    Germany,	Nick
    UK,			James
    USA,		James
];

//	Concatenate Table2 to Table1 to have a table with all possible mappings
Table2:
Concatenate(Table1) Load * Inline [
    Country ID,	Name
    Japan,		Nick
    Thailand,	Alice
];

//	Load all Names
Table3:
NoConcatenate Load * Inline [
	Name
    Nick
    James
    Alice
    Bob
];

//	Join the mappings
Left Join(Table3) Load Distinct
	Name,
    "Country ID"
Resident Table1;

//	Fill unmapped values
Final:
NoConcatenate Load
	Name,
    If(IsNull("Country ID"), 'unmapped', "Country ID") as "Country ID"
Resident Table3;
	

Drop Tables Table1, Table3;