Conditional based joining two column values from different tables

[Yoga321]

Hi all, 

I am new to alteryx i have a scenario of containing two tables as shown below

Table 1:

Emp	Type	City
A	Work	Brazil
A	Hometown
A	Leave
B	Work	America
B	Leave
B	Hometown
C	Work	Africa
C	Hometown
C	Leave

& 

Table 2 as below:

Emp	Hometown
A	America
B	Africa
C	Brazil

My expected output is joining only the city's where Type is hometown in the table 1 with Hometown in table 2 and must display as 

Emp	Type	City
A	Work	Brazil
A	Hometown	America
A	Leave
B	Work	America
B	Leave
B	Hometown	Africa
C	Work	Africa
C	Hometown	Brazil
C	Leave

Anybody help in this!!!!!!!

Regards,

Yoganantha Prakash G P

[Thableaus · Accepted solution]

Hi @Yoga321 

See solution

- Add a Record ID to original Dataset

- Transpose lookup HomeTown table

- Join the datasets by Emp and Type = Name (Hometown records)

- Union the Left Side (no hometown records) with Join Side (hometown records) - Auto Config by Position in this case

- Sort by Record ID to put everything in order.

- Deselect Record ID

WF appended.

Cheers,

[Yoga321]

Thanks Thableaus!!!!!