Loop/variable/xls sheets

KNIME Analytics Platform
1

Hi all,

Can someone help me how I can easily create different excel output files for different countries?

My data looks like this

Country … person … amount
Belgium … pers 1 … 100
Belgium … pers 2 … 200
Germany … pers 3 … 300
UK … pers 4 … 400

What I want is one folder with 3 files. In the file name I want to see the Country (+ if possible other text --> Sales Belgium/Sales Germany/Sales UK)

How can I make a variable based on country that I can use to divide the files and can I use this to determine which file to use as final result (I want to write to a certain tab on an existing xlsx file -->Excel Sheet appender). And how can I build this in a loop?

Remark: there are about 30 countries, so I want to have 30 files, depending on the available countries in the column “country”

Kind regards,
Tienemientje

2

Extra info in case it is not clear: in file Belgium, I want to see 2 rows. In files Germany and UK I want to see 1 row in each file.

3

Hi @Tienemientje

I created an example workflow which firstly creates a list of all distinct countries and then loops over that list. In the loop the original table is filtered on each invidiual country. Inside the Wrapped Metanode I create a filename based on the country and some prefix (“Sales”).

I set the location to knime://knime.workflow/…/, so the files will end up in the same folder as the workflow (refresh the KNIME Explorer to see the files. You can change the location of course.

country_excel.knwf (17.1 KB)

Preview:

example_excel_files
example_excel_files982×383 34.4 KB

4

You could also use a Group Loop Start node (see https://nodepit.com/node/org.knime.base.node.meta.looper.group.GroupLoopStartNodeFactory) which would give you all rows for a given country in an iteration, and a flow variable containing the name of the country.

Steve

3 Likes