Full outer join in join node and keep original row ID

[anguslou]

I want to merge different columns in different tables with same set of row ID, however some rows may be missing in some tables, for example:

table 1:

rowID	color
0	yellow
2	red
3	red

table 2:

rowID	fruit
0	banana
1	apple
2	apple

I am expecting following table:

rowID	color	fruit
0	yellow	banana
1		apple
2	red	apple
3	red

I tried full outer join and keep row keys which is denied by joiner node as “Cannot reuse input row keys for output”. Any workaround?

[aworker]

Hi @anguslou

My suggestion here would be to use two -RowId- nodes to extract the row ids from both tables before joining them and then use a -Rule Engine- node followed again by a -RowID- node to set the new wanted values of the joined table row Ids.

Hope it helps.
Best
Ael

[mlauber71]

@anguslou here is an example. It is not pretty but it would work

Handle RowIDs when using Joins – mlauber71