Joining two tables with different sorting

I have an excel-table with a part-ID and some other information in the different columns. Now I have a second excel-table where there is also the part-ID and then in the other columns other information about that part. I want to join those two tables so I get one table with part ID and then all the information to that part out of the two tables. The joiner node doesn’t work for me. Probably because the sorting of the tables is different (so the same part ID isn’t in the same row). Furthermore the second table only contains some of the parts that are listed in the first table.

I would be glad if anyone could help me by sharing his idea on how to solve this task.

Hello @gungero,

and welcome to KNIME Community!

Joiner node is definitely a way to go in your case and believe it’s just a matter of configuration to get output table you wish for. Is there possibility to share your workflow with us? If data is confidential then dummy data works just fine as long as it represent real one good enough. If that is too complicated some screenshots can also help :wink:

And additionally what KNIME version are you using?

Br,
Ivan

4 Likes

Hello Ivan,

unfortunately the data is highly confidential, I just tried to make some dummy data but I can’t upload documents here due to company IT limitations. Maybe I will create some dummy data on my private PC at the weekend. For now:

I am using version 4.3.1.
For now I used a full outer join, bc i want to keep the information about those parts, that are only listed in one of the tables. But as a result of the joiner I only get a table where both tables are “repeated”. the first table is copied with the columns of the second table all empty ("?") and below that it is the same with the second table. From looking at the tables manually I know that there are some IDs that are the same and should result in a join. Most of the IDs are listed multiple times in the excel data. Maybe that could cause the problem?

Hello @gungero,

I see. Ok. How about configuration window of Joiner node then? If a single match is not found could be that matching columns have different type. Can you check that?

Br,
Ivan

Type Difference? That’s an evergreen :sweat_smile:

1 Like

Joiner is a case sensitive. Join fields may have extra spaces so on.

2 Likes

Yeah I thought about that too. Type is the same, both String (“S” in column header).

That could be the problem I will look it up in my data again. I guess I have to remove some “space chars” (" ")

1 Like

Hello @gungero,

did you have some progress with your task? Or maybe with dummy data? Anyways configuration window of Joiner node might help as well…

Br,
Ivan

1 Like

Hello Ivan,

yes I solved it, sorry for not keeping you updated. there were some whitespaces, I removed them with String manipulation and the strip function.

Thanks to all of you here!

1 Like

Glad to hear that @gungero.
Ivan

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.