Column Join on partial string match?

Hello there, (my first post) I hope I’m not duplicating anyone else’s request here.

The Joiner Node works so well with it’s variety of join methods. But I am wondering if it’d be possible to perform a column join between two tables based on a match of say, the first 7 left hand characters, which in Excel would look like "=VLOOKUP(LEFT($A2,7),TableName,Column,FALSE).

In my example I have a table of ‘account codes | company names’ like: F612818 | Exquisite Widgets.

I then have a table of thousands of servers and computers for all clients where the hostnames start with the Account code (eg: F612818sg2sl03).

The goal here would be to achieve a second column in the table of servers showing which customer each the server belonged to.

Does that make sense?
Maybe this is a new feature request, maybe it can already be achieved, I’m hoping to understand either case.

My thanks in advance
Cardinal.

Hi @Cardinal_Fire,
You can do this in KNIME by creating a dedicated joining column that just contains these first 7 characters. An easy way would be with the String Manipulation node, I have attached a workflow that demonstrates how to do this. Joining example.knwf (9.7 KB)

best,
Gabriel

2 Likes

Superb,

Gab1one I felt it was a String Manipulation task but couldn’t conceive how it would be used. That worked perfectly and my goal is achieved. Thank you.

One last “noob” question: how the devil do you get “curvy” connector lines? :smiley:

Thank you again,
Cardinal.

To set it globally for all future workflows:
Preferences -> KNIME -> Workflow Editor -> KNIME GUI -> Workflow Editor

to change the setting for the opened workflow, press the button highlighted with the orange box:

best,
Gabriel

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.