Dear Knime Forum,
hereby I’d like to ask for your kind support.
I built a Cost Center charge out work flow as attached. (upper section)
My challenge is to combine two excel data sets into one file on two separate sheets.
In the section"Creating Charge out files by Cost Center" the workflow creates several cost center workbooks with a sheet including the summary by cost category but I also want to add to the same workbook, on a separate sheet, all the according cost center line items.
Summary of line items by Cost Category is grouped by the Groupby node.Cost Center Charge out.knwf (107.8 KB) Cost Center Charge out.knwf (107.8 KB)
@MLauber71: Thanks a lot for taking you time to look at my issue. In fact I did consider the Excel Sheet Appender node. My issue was that I did not find the solution that with each iteration of the loop by Cost Center, the Excel file could be updated on one sheet with the summary and on another sheet with the according line items…
Your example does not contain any data so it is not easy to interprete.
If you want to collect lines and add them at the bottom of your sheet that is not possible with standard Excel nodes. You would have to collect the data first or revert to some Python things with openpyxl - but that might be overengineered.
To collect data you could ‘append’ them to a KNIME table like in this example:
Hello! Basically what it should do is to write the data set output of groupby node on one sheet (What it is currently doing with this workflow) and on another sheet ,in the same Excel file, the data set as it would be without application of the GroupBy node for the according cost center. I wonder how I can coordinate/set this up with looping. I wonder if that is somehow possible.
Not completely sure without having the data and try for myself but if you create the group loop and create the file name then you should be able to save the output location to a variable and if so could you maybe extend your workflow and use the excel sheet appender (as proposed by mlauber71) at the end to open the file using the variable as path and then append the data?
@mlauber71 and Daniel_Weikert. Thank you both very much for your hints. Based on your feedback I figured out the set up by adding the Excel Appender node and setting the file location as flow variable. As shown.
Again, many thanks!