Joining Spreadsheets

Joining Spreadsheets

In this workflow, we see how to use the Joiner node to clean up a dataset using KNIME Analytics Platform.

Sometimes more than a VLOOKUP is needed. You may need to compare two datasets by joining them for a shared column key and see which rows they have in common and what is missing from one and another side.

In this case, we need to eliminate those athletes who have never participated in a Summer Olympic Games edition from the Olympic_Athlete_Bio" spreadsheet.

We will see how to do it node by node:

To view the configuration for each node, either 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 - Top one contains all the results for any Summer Olympic games event, and the low one includes the athletes' bio. In this last one, some athletes only appear in Winter Olympic games editions.

3 - Within the "Data Area" tab, we have both selected read the "Whole sheet" since the spreadsheets already have a standard format (non-empty rows or columns, etc.).

Joiner node:

1 - We take the athletes' event results from the top node input as a left table. One athlete could participate in more than one event or more than one Olympic Games edition.

2 - The right table includes athletes' bios from the low node input. The table contains information about Summer and Winter Olympic Games editions.

3 - We join the tables using the "athlete_id" column. Then we flag all options in the "Include in output" section. Finally, we select "Split join result into multiple tables" from the output options.

4 - In this way, in the third output port of the node, we will find all the athletes that have never participated in a Summer Olympic Games edition.

Column Filter node:

1 - We retain only the "athlete_id" column from the unmatched right table rows. We will use it as a reference to exclude the winter athletes.

Reference Row Filter:

1 - Taking the "athlete_id" column from the athlete bio spreadsheet as a "Data column" and the "athlete_id" column from the previous node as the "Reference column", we Exclude all the winter athletes from our original spreadsheet.

Excel writer node:

We have configured the node to generate a new file called "Olympic_Ahtlete_Bio_cleaned_up" in the workflow data folder.

After running the node, the spreadsheet will open automatically.