Using Joiner, Left Unmatched Rows, Yielded Red ? Mark

[mphilip]

Hello Everyone!

I’ve been doing some research on my issue and exploring various solutions.

The case, input excel sheet with a log, input excel sheet with a “dictionary” of values, join the test type from the dictionary file to the log and filter out the specific test type. Here is what the sample data looks like:

Log

Dictionary

Posing an example using the data outlined above, in theory the join should show that SS1, LV2, etc… are NONTESTS. No matter what I do and which approach I take I don’t get the right result.

Thanks for all your help in advance!

[rfeigel]

What output do you get - only missing values? What kind of join are you doing? Are you sure there are no hidden spaces (or other characters) in the respective test code columns? It would be a lot easier to try to help you if you could upload sample tables and your current workflow.

[mphilip]

@rfeigel Thank you for the reply.
I did look into that, just to be safe I used the string manipulation node to strip.

This is the output i’m getting:

I’ve attached a folder with workflow & sample files.

dictionary.xlsx (9.3 KB)
for knime post.knwf (14.2 KB)
log.xlsx (9.2 KB)

[rfeigel]

The string lengths don’t match. I’m headed to bed. I’ll take another look tomorrow.

[mphilip]

Appreciate it!

If I’m understanding correctly, excluding white space?: they don’t. The “test code” varies in length

[rfeigel]

There are hidden characters in the strings. Strip them out as follows:

[mphilip]

@rfeigel were you able to get there proper result after doing this?
Even after doing the string manipulation I am facing the red question marks when joining. On a side note, how were you able to detect those hidden characters?

Thanks!

[takbb]

Hi @mphilip ,
Welcome to the KNIME community.

As you are using KNIME 5.2, you can also possibly make use of the new String Cleaner node to help you here. As has been mentioned already, there are differences in the codes in the two tables in the forum of space padding.

Adding the String Cleaner after both Excel Writers for good measure, they can be set to remove leading and trailing spaces and a variety of other potential problems:

(there are other options available too further down the dialog)

for knime post - String Cleaner.knwf (106.0 KB)

Now setting the joiner to output “matching rows”

… it joins the following:

[mphilip]

I gotta start keeping up with the new nodes coming out

Thank you so much @takbb !!!
This one worked to see all the ones that are matched. But I want to see matched and unmatched, where it is unmatched it would be blank there. When trying the left unmatched. I checked Matching Rows and Left Unmatched Rows

Really appreciate the help.

[rfeigel]

The regexreplace function in the screenshot removes hidden characters just like the String Cleaner node @takbb suggested. His approach and mine produce the same results although the String Cleaner is probably easier to use.