Joiner doesn't work

Hi there!

I’m a new user to KNIME and I wanted to join 2 very simple table but the joiner just can’t seem to find the matching rows in the second table. I can confirm that there’s multiple matches.
Capture
I’ve attached a sample work flow where I isolated the problematic columns.
Appreciate your help!

joiner issue.knwf (80.5 KB)

Hello @cyin0304 and welcome to KNIME forum.

What is currently happening within your lower table in workflow; it has an invisible character joined to the string.

image

I suggest you to use a ‘String Manipulation’ node with the following code, just before the joiner:

substr($Item ID$, 0, 15)

BR

4 Likes

Hi @cyin0304 and welcome to the Knime Community.

I am sure the joiner node works perfectly fine :wink:

When we expect some records to join and they don’t, it is simply because the records do not match. In most, if not all cases they may “seem” to match, and that is because there are some hidden characters that make the differences that we don’t see.

One way to prove this is to concatenate some strings before and after the values. For example:
join("XXX", $Item ID$, "XXX")

You first excel file seems to be in order. However, looking at your second file, this is the results that I get:

As you can see, there’s a “space” between the values and the trailing XXX. In reality, it’s not a “space”, but rather a “special” character that Knime cannot display in the current character encoding.

In theory, these should not match - I mean even after finding this fact, as they are, they do not match. If they should, then it’s a data issue.

If you absolutely must make them match based on on the numbers only as ID, you can manipulate the 2nd table with:
regexReplace($Item ID$, "[^\\d]", "")

This expression will remove any characters that are not numerical.

image

Results:

Workflow: Joiner issue_Bruno.knwf (89.3 KB)

7 Likes