How do I merge a column from one table into the column of another table that has missing entries?

I have two tables with different column names and different number of rows. Column 3 of table A for example is missing some entries that are present in column 2 of table B. I want to add the missing entries from B into A based on the values in another column, say column 2 of table A. However, not all the same entries are present in the two tables for the key.

For example, the two tables look like this:

 

A = table({'John','Jane','Jim','Jerry','Jill'}',[1;2;1;3;1], {'', '', '', 'Mat',''}', ...

    'VariableNames',{'Employee' 'Department', 'Manager'})

 

B = table([1 2]',{'Mary' 'Mike'}',...

    'VariableNames',{'Department' 'Manager'})

 

If I try using the "join" function, I get an error because there are more Departments in A than in B.

  >> join(A,B)

Error using table/join (line 128)

The key variables for A and B cannot contain any missing values.

  How can I do this?

Answer 1

[MathWorks Support Team]

There are two possible solutions depending on the output you expect to see:

1. Using "outerjoin"

Using the "outerjoin" function and specifying the variable "Keys" to use to merge the tables produces the output:

 

C = outerjoin(A,B,'Keys','Department','MergeKeys', 1)

 

2. Using "ismember"

Using the "ismember" function to determine the matching rows in A and B and adding the missing entries in A from B produces the output:

  [idxA, idxB] = ismember(A.Department, B.Department);

A(idxA,'Manager') = B(idxB(idxA),'Manager');