Icon

Workflow Demo Joiner Components

Workflow demonstrating the use of the Joiner Components

The joiners provide the following functionality:

Join Between: source column BETWEEN values in two lookup columns

Join Like: source column LIKE a wildcard pattern in a lookup column

Join Contains: source column contains a value in a lookup column (lookup is substring of source)

Join Reexp_Like: source column matches a regular expression pattern in a lookup column

Join Custom Condition: write a sql where clause to join two tables. This is useful for more complex/multiple conditions but requires basic sql syntax knowledge

Updated : 16 May 2023 - Additional of Join Contains Full Outer which returns three outputs Join, Left Unmatched and Right Unmatched

Enrichment(Lookup)Tables How to use a LEFT OUTER joinCurrently supported on Join Between, Join Regexp_Like Join Custom Conditionand Filter Joiner The filter joiner performs a similar operation tothe Regexp_Like joiner, but does not make use ofan internal H2 database. It is worth trying as analternative as performance may be better orworse under different circumstances. NEW MAY 2023Component based on the Join Like and Join Contains components but also returnthe unmatched rows.They also has an option to return the rowids from each tableThis same technique could be used to provideouter join support on the other Join components, which I may add over time, sowatch this space Inner JoinSample Source DataAge | NameLow|High|DescriptionInitial Wildcard | NicknameContains | CommentRegex Pattern|CommentstartsWith | endsWith | CommentLookup DatesDatesLeft OuterJoinJoin Between Extract Data Tablefrom Delimited Text Extract Data Tablefrom Delimited Text Join Between Join Regexp_Like Join Like Join Contains Extract Data Tablefrom Delimited Text Extract Data Tablefrom Delimited Text Extract Data Tablefrom Delimited Text Join CustomCondition Extract Data Tablefrom Delimited Text Table Creator Table Creator Regex LookupFilter Joiner Join Between Join ContainsFull Outer Join LikeFull Outer Enrichment(Lookup)Tables How to use a LEFT OUTER joinCurrently supported on Join Between, Join Regexp_Like Join Custom Conditionand Filter Joiner The filter joiner performs a similar operation tothe Regexp_Like joiner, but does not make use ofan internal H2 database. It is worth trying as analternative as performance may be better orworse under different circumstances. NEW MAY 2023Component based on the Join Like and Join Contains components but also returnthe unmatched rows.They also has an option to return the rowids from each tableThis same technique could be used to provideouter join support on the other Join components, which I may add over time, sowatch this space Inner JoinSample Source DataAge | NameLow|High|DescriptionInitial Wildcard | NicknameContains | CommentRegex Pattern|CommentstartsWith | endsWith | CommentLookup DatesDatesLeft OuterJoinJoin Between Extract Data Tablefrom Delimited Text Extract Data Tablefrom Delimited Text Join Between Join Regexp_Like Join Like Join Contains Extract Data Tablefrom Delimited Text Extract Data Tablefrom Delimited Text Extract Data Tablefrom Delimited Text Join CustomCondition Extract Data Tablefrom Delimited Text Table Creator Table Creator Regex LookupFilter Joiner Join Between Join ContainsFull Outer Join LikeFull Outer

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: bbate
Created at: 2022-11-12
On NodePit since: 2022-11-13
Last update: 2024-04-27
Created with KNIME version: v4.7.1
Tags: JoinWildcard matchingRegex MatchingRegular ExpressionsH2 Database Join

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

Try NodePit Runner!