Column Join on partial string match?

[Cardinal_Fire]

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.

[gab1one]

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

[Cardinal_Fire]

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?

Thank you again,
Cardinal.

[gab1one]

@Cardinal_Fire

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

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