Join KNIME table by like

What node I can use to join two KNIME tables by like criterion.
Ex. $Organization_T1$ like ‘$organization_T2

Igor

One way, although it is going to get heavy-going if you have large tables, is to use the Cross Joiner node to join every row of the first table with every row of the second table, and then use something like the Snippet Row Filter or Rule-based Row Filter to filter down to just the rows you want

Steve

1 Like

I hope there is more efficient way. Anyway, this type of functionality could be added to KNIME.

A second, maybe more efficient way would be to bring the contents of your two join columns into a canonical form first and then do the join. However, I’m not sure what the definition/expected behavior of your ‘like’ criterion is?

– Philipp

1 Like

I assume Like functionality as in SQL or by string distance.

Hi,

Using SUBSTR function in “String Manipulation” node, create new column
in both tables, that will be used as a joining column in “Joiner” node.
Number of chars in SUBSTR function (third parameter) depends on the way
you want both columns to be compared.
E.g. if “Organization” LIKE “Org*”(at least first 3 chars must be equal),
the number of chars = 3

SUBSTR(column1,0,3)

Then make left join of both tables on this key, where left table is
on the left side of LIKE comparison.

Martin K.

2 Likes

Thank you, Martin.