Merging two dissimilar tables together

Hi there,

I have a problem, how can I merge two different tables together, using the same primary key. For example,

Table1:

Key, ValueTable1
SN1, 1.3
SN2, 0.5
SN3, 2.3

Table2:

Key, ValueTable2
SN1, 1.8
SN3, 0.4

Merging these two datastreams to create the table;
Output table:

Key, ValueTable1, ValueTable2
SN1, 1.3, 1.8
SN2, 0.5, ?
SN3,2.3,0.4

I have tried to use concatenate node, and joiner, but I can't get the output table needed.

Thanks in advance for your help,

Stanage.

Hi stanage,

The Joiner should exactly do this, using any of the outer join methods, depending on how you connect the two input tables to the joiner node. I assume a full outer join is what you need.

Regars,

Thorsten

Hi Thorsten,

It will work when I choose rowID, but it does not merge the tables, instead you have a misalignment of the two tables.

What I really need, is to merge the data fields based on the selected key SNx in the example. When I try this with the joiner, I get an error message 'Execute failed: Encountered duplicate rowID "row0" at row number 3235" (the joiner options are as follows;

Duplicate column handling (checked)
Join Mode (Full outer join)

Really what I am after is to merge, and align two tables, using a primary key, and the ability to insert null rows in the join if no match is found from the primary key join. Have you any node that can do this?

Stanage

Hi stanage,

Hm, that is strange. This error message does not come from the joiner directly but from adding the joined rows to the output table. There a row with a duplicate ID arrives, which should not happen. Could you look into KNIME's log file and send me the full stack trace of the error message? And - if possible - also a small dataset with which I can reproduce the behaviour?

Thanks,

Thorsten

This is solved. When using the Joiner one has to keep in mind, that the "column" from the first table which is used in the join is always the row ID. If the values upon which you want to join are in a column, you have to use the RowID node before and replace the row ID with the column.