Merge Excel files information

Hello, I need to join the information of these two files no matter if they have some empty information, I only need to join them by the code which is the information they share, however when doing the JOIN I understand that there is information that is not in a file and in another yes, but there is another that is in both files and still brings me empty (eg: cod 10092-1) how else can I join the complete information.

Excel.knwf (24.6 KB)

Hi @slunah,

The solution would normally be to specify the “outer join” conditions on the joiner node, but I think you are doing that.

However I cannot see your data … as unfortunately your Excel files are located elsewhere on your pc, which means it cannot be executed.

Can you either upload the excel files, or alternatively you can execute the workflow on your KNIME, so that all the nodes have gone “green”, (the main thing is that the two Excel Reader nodes are showing “green”) and then export it again but this time untick the box that says “reset workflow” on the export dialog. Then reupload the export.

image

thanks

Hi, I share you de workflow without reset

Excel.knwf (838.7 KB)

Hi @slunah, in this second uploaded workflow, it looks to me like it is joining as you want.

You have it set to return matched, left only and right only, which is correct.

Previously I think the config on the joiner node was to “Split join result” between the three output ports, whereas now it is not set, so the top output port gets all the rows whether they matched or not.

Currently “Row keys” is set to “assign new keys”. You could change this to concatenate and then you can see which rowid from each table (LeftRowid_RightRowId) the output row has joined.
e.g.
image
Here you can see that 10092-1 is present but was not found in the right table.

Let me know if I have misunderstood what you are asking, but it looks to me like it is now working.

3 Likes

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