Combining two different tables with columns that not all are the same

[MRKoura]

I’m facing this issue that could be easy but somehow I’m not able to resolve.

I have two tables that some of their columns’ headers are the same but data content are different, and some other columns are completely different.

below is an example for my issue:

• The first table has columns (Name, Gender, Age, Employer & Job Title)
• The second table has columns (Name, Gender, Age & Status)

As seen from the two tables there are 3 columns that have the same header. The problem is that some of the data in table 1 are not in table 2, and what I need is to combine the table and if there is any match between the 3 similar columns on a certain row, to add the data combined from the two tables in a single row.

To be specific, I need the final table to have (Name, Gender, Age, Employer, Job Title & Status) with no duplicates and for the missing values they are to be blank in case missing from any of the tables to be joined

Can anyone help with this?

[badger101]

Hello. Try these:
Concatenate Node with default settings to join the two tables followed by —> GroupBy Node with the settings as such:

Group tab: Choose the first column (Name).
Manual Aggregation tab: Select all remaining column (Gender etc.) then change the Aggregation settings to Unique Concatenate. Uncheck the Missing values boxes.

[elsamuel]

I’d try a full outer join using the Joiner node, with Name, Gender and Age as the matching columns. You might have to play around with the column retention settings.