Question marks in the join result

[polina_l]

Hello,

I experience the same issue as was described here

Joiner not working?

Hi all, I am trying to create time series with regular time step. To begin with I have irregular step time series. I used “Create Date&Time range” node and wanted to join it with existing table, and as a result I got question marks. Here is a table with irregular time step [image] Here is a “Create Date&Time range” result [image] Here are joiner settings [image] and here is a result [image] Does anyone have any idea why?

I tried to join a few tables and got a problem with one of the joins: the resulting columns consist of ‘?’ Other tables did join ok.

Unfortunately there was no resolution in the previously reported issue. My machine restart did not help.

Many thanks!
Need it urgently for a final study project

[ScottF]

Hi @polina_l and welcome to the forum.

Can you upload a sample workflow showing your progress so far? Hopefully in executed state, assuming the data you are using is not confidential. That will help the forum experts diagnose your problem better.

I suspect it’s something in the Joiner configuration, but without more info it’s hard to be sure.

[polina_l]

TestJoin2.knwf (536.7 KB)

Hi Scott,

Please see the workflow

Polina

[polina_l]

Other joins with other tables worked ok with the similar configuration

[bruno29a]

Hi @polina_l and welcome to the Knime Community.

Every time we get unexpected results from joins, it’s either because:

1. we are not doing the joins properly,
   or
2. the data we are joining on are not what we expect them to be

The first one is relatively more obvious to find, and can be fixed with the proper configuration, while the second case is more tricky, especially if you have “hidden” characters in the data. As the name says it, the would be characters that are there, but not visible, for example a space at the end. That would make the data different from the one that does not have a space at the end, but to the naked eye, you would not see the difference since the space is invisible.

At the end of the day, the joiner will always give you the results that it was ask to retrieve based on the data it is given.

Looking at your workflow, you can see that you are joining an integer column with a string column. That can be a problem. Also, this can also be a hint that the column does not entirely contain numerical values. I mean, both Excel Readers are reading similar columns, but why is the ID being read as an int from the Customers file, and why is the ID being read as a String from the Sales file? There must be some funky characters in the Sales file that made Knime choose to read the column as a String.

You can force the Excel Reader to read the column as a numerical value - go to the Transformation tab in the Excel Reader (I can’t do this from your workflow as it does not include the Excel files), or you can convert the column to Int, which I did since I could not do the first option as I don’t have the files. Once I converted, I got some joined results:

There are a few ways to convert the column, but the easiest and fastest one is probably using the String To Number node:

Just make sure you configure it as shown in the screenshot.

And add it here:

[polina_l]

Hi Bruno,

Thanks a lot

[polina_l]

It works!

Also it works if I select to compare values in the join columns by string representation

Could you also clarify how to look the types of the joining columns? To be sure I don’t encounter this problem again with other data

Thank you

[bruno29a]

Hi @polina_l , what I highlighted indicates the column types:

I: Numeric Integer
D: Numeric Decimal
S: String
etc

Similarly, you can see the column types in the column headers:

[polina_l]

I see now,
Thanks again