Match and merge

Hello All,

I have two spreadsheets of excel. I have one common field between them. I want to merge two Excel sheets if the values in common field matches. I want to keep my all columns in the newly merged spreadsheet. Please advise. Attached here excel sheets. 

 

Hello,

you can use the Joiner node to perform an inner join on the common field.

Cheers,
Marco.

@mMarco_ghislanzoni, thank you for the reply. I tried the Joiner node before too but no luck. Image attached here with. I would appreciate if you provide me layout. Thanks

 

Gurpreet

 

Hi Gurpreet,

I think you have 2 issues here:

1. Your phone numbers in the Excel file all look like "regular" numbers, hence when you read them in KNIME with the XLS Reader node they become Double. I suggest you create a separate column giving them a string-like prefix, for example:

=CONCATENATE("P", <phone number>)

This will ensure they are read into KNIME as String which is what you want to have to match phone numbers. You can always remove the "P" prefix in KNIME or keep it since it doesn't make any difference for the string match.

Also, one of your XLS file has 3 trailing rows (one summary row and two footer rows) which are confusing the XLS Reader node regarding to what data type each column is. You should remove those rows before reading the XLS into KNIME.

2. I checked through your sample data and there are no matching phone numbers between the two data sets. This explains while the Join is empty. If you try to copy a bunch of phone numbers from one table to the other to make sure there are matches, you will see that the Join table is not empty.

Your configuration for the Joiner node seems correct, so with the changes above it should work as expected.

Cheers,
Marco.

@Marco_Ghislanzoni, Thank you Marco. It works for me. I really appreciate your help.

Gurpreet