Join Strings (Multiple Sheets)

This workflow shows how to concatenate tables from two different sheets and combine string-type columns using KNIME Analytics Platform.

The aim is to concatenate different sheets and combine the sport and event columns from the "Olympic_Athlete_Event_Results" file.

Let's walk through the different nodes involved in this operation:

To view each node's configuration, double-click or right-click > "Configuration" to open the configuration dialogue.

Excel Reader nodes:

1 - In the "File and Sheet" tab, the file name and the sheet to read from are already set up (the folder with the data is already included when you download the workflow).

2 - Within the "Data Area" tab, we have selected read the "Whole sheet". This configuration allows us to have the sheet as it is. The intent is to respect its original structure.

Concatenate node:

1 - We can control how the two input tables will be concatenated. Since our two input tables have the same structure, we use the intersection mode. There is the option of either using intersection or union columns mode.

2 - We choose the "Append suffix" option to handle duplicate row ids with the suffix "_dup". It means that if we have two rows with the id "Row0", one gets changed to "Row0_dup".

Column Combiner node:

We combine the columns "sport" and "event" to the column "discipline" using ":" as a delimiter and also remove the aggregated columns from the output table.

Excel Writer:

We will append a new sheet called "Combined_Sport_Event" in the original excel file (Notice that if you run it several times, it will overwrite it).

After executing the node, the file will open.

As you can see from the output, we now have concatenated the input tables and replaced the "sport" and "event" columns with the combined column "discipline".