Conditional join of two tables in Knime

Question

I am new to Knime Analytics.

I have two tables and I need to join them not by equality, but by the difference between the values of two fields. (In sql it would look like "table1 join table2 on abs(table1.mass - table2.mass)<0.005 ") but in nodes I have found only node, that join by equality.

Are there any nodes for conditional joining tables or something like this?

Answer 1

The only way I can think of to do this is as follows. Use a Cross Joiner node to join all rows of each table to all rows of the second table. Now use a Java Snippet Row Filter on the joined table, with the following snippet code

return Math.abs($mass$.doubleValue() - $mass (#1)$.doubleValue()) < 0.005;

(Assuming that both incoming tables have a column called 'mass', which will become 'mass' and 'mass (#1)' after the Cross Joiner