join into left table

[Heinz]

Dear all,

I have two tables:
left: Table A with column Field A1, A2
right: Table B with column Field B1, B2

I join both tables with matching rows, left unmatched rows, key is field A1 to B1
In case of match I would like to fill table A column A2 with value table B column B2
In case of nomatch table A cloumn A2 should left untouched

How to do best way?

Thanks for help!

BR,

Heinz

[takbb]

Hi @Heinz

If you had two tables as follows:

Table A:

A1	A2
A	V
B	W
C	X
D	Y
E	D

Table B:

B1	B2
B	A
C	L
D	I

You could join them using A1 = B1 and configure it to output column B2 from Table B (no need to output B1), and send all “matching” and “left unmatched” rows to the same port:

After that the Column Merger could be configured as follows:

which would result in Column A2 being populated with the values from B2 where they are not “missing”:

Then a Column Filter can remove the B2 column.

Alternatively…

Assuming KNIME 5.x, an alternative to the Joiner node would be the Value Lookup node , configured as follows:

This would be followed by the Column Merger and Column Filter as before.

(On KNIME 4.x, the Cell Replacer is similar to Value Lookup)

[rfeigel]

Try this:
Left Join Match.knwf (76.8 KB)

[Heinz]

Thank you very much for help!