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”
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.
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)
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.