Spreadsheets and Loops

This workflow shows how to append additional sheets to an existing spreadsheet with a loop using KNIME Analytics Platform.

The aim is to create new tables from the input spreadsheet and append them as new sheets with a custom name provided by flow variables. We use the athlete event results spreadsheet that contains data from the 1896 summer Olympic Games.

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

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)

Group Loop Start node:

1 - The Group Loop Start node groups the table by the given group columns and iterates over the grouped tables. In this case, we want to group the athlete event results by the "sport" column. We manually include only the "sport" column as a grouping column.

2 - Besides iterating over the created groups, this node also provides iteration information from flow variables. If you right-click the Group Loop Start node and choose the option "Grouped Input", you can see in the "Flow Variables" tabs the variables "groupIdentifier", "sport", "currentIteration", "Loop-Execute" and "Loop (0)". We are interested in the "sport" variable because it represents the current group name in the current iteration, which we will use for the sheet names.

String Manipulation (Variable) node:

1 - To create a custom name for each new sheet we want to append, we use the "sport" flow variable created by the Group Loop Start node. The sheet name is built using the "join" function, which joins the flow variable with the year of the Olympics.

2 - We give this variable a new name by selecting the "Append Variable" radio button and entering "sheet-name".

Rule Engine | Row Aggregator | Sorter nodes:

1 - Using the Rule Engine node, we define the rule: if there is a medal (any type), then one. If not, then zero.

2 - The Row Aggregator node sums the medal grouping by Athlete.

The goal is to have the list of the athletes by sport sorted by the number of medals they won in the 1896 Olympic Games edition for each new sheet.

Excel Writer node:

1 - We choose the input file from before and select the "append" radio button to append new sheets. To reuse the workflow again, we choose the "overwrite" radio button in the "If sheet exists" option.

2 - To set the sheet name to the flow variable "sheet-name" we created before, we go to the "Flow Variables" tab in the configuration dialogue. Here we select the variable "sheet-name" for the "sheet_names" flow variable.

Variable Loop End node:

Since the Excel Writer node has no table output port, we use the Variable Loop End node to close the Group Loop.

You can consult the output by opening the excel file located in your workflow's data area: <knime-workspace>/workflow_group/my_workflow/data/
The data folder is stored inside the workflow (stored in your local machine).

As you can see from the output, we appended a sheet for each sport with the defined custom name, and the athlete medals sorted list.

TL Exercises