Certain values are not recognized, empty data table

I have a workflow that loops through account ID numbers and builds a regression model for each. The dataset has many rows for each account so the Group Loop segments the table for each loop iteration.

It works perfectly for most of the data, but for certain account IDs, the value is not recognized when used as a joining column. The join returns an empty data table even though the value is definitely in both tables. Thus there is no data to feed the regression model and the workflow is useless for this entire account.

The account IDs are stored as Strings so I have tried stripping the whitespace and converting to numbers, but the value is still not recognized when I try to Join and Filter on it with many different nodes. I am copying the value directly from the table and trying to filter on it but it doesn’t work. What do I do when the value is clearly in the table but cannot be recognized or processed by any nodes?

Hi @cbirch and welcome to the Knime Community.

Quite often what happens is that there might be some “hidden” characters, for example a white space before or after the data that you don’t see, but makes the data different from one table and another.

One test that you can do is prepend and append some strings to your data just to see what’s there. For example, via the String Manipulation, you can apply this:
join("XXX", $Your_Column$, "XXX")

So, if your data has “hello”, you should see “XXXhelloXXX”. But if your data has “hello ” (with a white space at the end), you should see “XXXhello XXX”. Similarly with “ hello” you will see “XXX helloXXX”, etc

You can clean up all your columns to remove white spaces beginning and at the end of your data using the strip() function via the String Manipulation (Multi Column). Just do:
strip($$CURRENTCOLUMN$$)

EDIT: Sorry, I had not noticed that you said you have tried to remove the white spaces. Then you should still do the test that I suggested (prepend and append “XXX”), as it will also check if there are any “hidden characters” apart from white spaces.

4 Likes