Icon

comparison test - options for non-equi row-match

Demo Options for matching based on non-equal values

Workflow demonstrating some ways to perform a join where part of the join condition is not based on perfect equality. The Joiner node can only join using "equal", so the joiner can be used to perform the part of the join it can produce, and then other standard nodes can be used together to filter those rows that match the remainder of the condition, in a similar way to a Rule Based Row Filter would work. In some cases a Rule Based Row Filter could be used here, but where calculations are required, the Rule nodes are not capable of the required matching, so String Manipulation or Math Formula nodes can be utlised instead.

Another option is to use Math Formula to provide the lower and upper range boundaries for one of the values and then use a Rule-Based Row Filter to keep only those rows where the other Value is within that range


Alternatively, a custom component can be used to provide a "custom condition" utilising a SQL condition compatible with the H2 database

https://hub.knime.com/takbb/spaces/Public/latest/Components/Joiner%20Components/Join%20Custom%20Condition%20-%20indexed

And a further possibility, for greater flexibility and allowing the query to be written using DB Query nodes, components can be used for creating copies of the data in an H2 database:

https://hub.knime.com/takbb/spaces/Public/latest/Components/Joiner%20Components/KNIME%20Table%20Session%20Connector

and

https://hub.knime.com/takbb/spaces/Public/latest/Components/Joiner%20Components/KNIME%20Table%20Connector

Using custom conditioncomponent to use a SQL query Emulating a "Rule Based Row Filter"...String Manipulation andMath Formula can both perform the samejob of flagging "matching" rows, similar tothe way Rule Engine would work if it couldperform calculations. Using "Table Connector"components to create databasetables that can then be acted on asif they are a database connection Calculate lower and upper limits and thenuse a Rule Based Row Filter to filter onlythose rows where Value is within range Calculate absolute difference betweenvalues and then filter those between 0 and0.05 Node 1Node 2Node 3Flag values within 0.05 of each otherFlag values within 0.05 of each otherFilter only within toleranceFilter only within tolerancetidy uptidy upJoin on code and t1.value between (t2.value-.05 and t2.value+.05)Uses previously createddatabase sessionand KNIME TABLE 2Initial Connector createsthe session and KNIME TABLE 1Write query as if theseare database tableslower limitfor value 2upper limitfor value 2Filter Valuebetween lower and uppertidy upabs (value1 - value2)Filter rows by differencetidy upExcel Reader Excel Reader Joiner Math Formula String Manipulation Row Filter Row Filter Column Filter Column Filter Join CustomCondition - indexed KNIME TableConnector KNIME TableSession Connector DB Query Reader Math Formula Math Formula Rule-basedRow Filter Column Filter Math Formula Row Filter Column Filter Using custom conditioncomponent to use a SQL query Emulating a "Rule Based Row Filter"...String Manipulation andMath Formula can both perform the samejob of flagging "matching" rows, similar tothe way Rule Engine would work if it couldperform calculations. Using "Table Connector"components to create databasetables that can then be acted on asif they are a database connection Calculate lower and upper limits and thenuse a Rule Based Row Filter to filter onlythose rows where Value is within range Calculate absolute difference betweenvalues and then filter those between 0 and0.05 Node 1Node 2Node 3Flag values within 0.05 of each otherFlag values within 0.05 of each otherFilter only within toleranceFilter only within tolerancetidy uptidy upJoin on code and t1.value between (t2.value-.05 and t2.value+.05)Uses previously createddatabase sessionand KNIME TABLE 2Initial Connector createsthe session and KNIME TABLE 1Write query as if theseare database tableslower limitfor value 2upper limitfor value 2Filter Valuebetween lower and uppertidy upabs (value1 - value2)Filter rows by differencetidy upExcel Reader Excel Reader Joiner Math Formula String Manipulation Row Filter Row Filter Column Filter Column Filter Join CustomCondition - indexed KNIME TableConnector KNIME TableSession Connector DB Query Reader Math Formula Math Formula Rule-basedRow Filter Column Filter Math Formula Row Filter Column Filter

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: Administrator
Created at: 2023-07-17
On NodePit since: 2023-07-17
Last update: 2024-04-27
Created with KNIME version: v4.7.4
Tags: Custom Query ConditionsNon-equi joinsFuzzy Joins

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!