Outerjoin tables with identical variable names and unique/non-unique keys

Hi

I have been trying to merge tables that have identical variable names with unique and non-unique key variables. In the example both tables have both a 'key' and 'Var1'. I essentially would like to match key variables, and horizontally concatenate them, and otherwise if they do not match then add them to the table. Easier to explain with a code example.

a = table({'a';'b';'c';'d'},[1;2;3;4],'VariableNames',{'Key','Var1'});
b = table({'a';'c';'e'},[0.1;0.2;0.3],'VariableNames',{'Key','Var1'});

c = outerjoin(a,b);

d = outerjoin(a,b,'MergeKeys',true);

The result I would like is:

    Key    Var1_a    Var1_b
    ___    ______    ______

    'a'      1       0.1   
    'b'      2       NaN   
    'c'      3       0.2   
    'd'      4       NaN   
    'e'    NaN       0.3

If I just use outerjoin like in table c above then I find that the tables merge, NaN is added to those that do not match up, and it correctly differentiates var1 between tables a and b. However, it does not merge keys that are identical.

Key_a    Var1_a    Key_b    Var1_b
_____    ______    _____    ______

''       NaN       'a'      0.1   
'a'        1       ''       NaN   
'b'        2       ''       NaN   
''       NaN       'c'      0.2   
'c'        3       ''       NaN   
'd'        4       ''       NaN   
''       NaN       'e'      0.3

If I use outerjoin like in table d var1 from table b overwrites var1 from table a.

    Key    Var1
    ___    ____

    'a'    0.1 
    'a'      1 
    'b'      2 
    'c'    0.2 
    'c'      3 
    'd'      4 
    'e'    0.3

Any thoughts? I can always go through and rename the variables for the tables but there are over 30 variables for each table and I have to merge 6 tables together total, so I was hoping to avoid that...

Thanks!

Answer 1

[Guillaume]

You need to tell outerjoin that only the Key variable is to be as key:

outerjoin(a, b, 'Keys', 'Key', 'MergeKeys', true)

[Daniel Westfall]

Ah, stupid me. Thanks!