I would like to connect multiple nodes to variable loop end, but not able to do it. I think its better to explain the entire process (bit lengthy though).
I have an excel file with some data, need to split the data, create excel files and send emails with attachment based on user name column. Each file should consist of 3 sheets (2 data sheets and 1 summary sheet). The data sheets are created based on of the column filters. For example if column “abc” has T&E, all rows pertaining to T&E should be written in sheet 3, otherwise it should write in sheet 2 and the summary is a combination of formulas (count of volume, sum of amount, average of aging along with graphs) of respective categories for Status column (screen shot as mentioned below).
I was able to build the workflow till writing the data to sheet2 and sheet3, below is the screen shot for your reference.
I assume that if we have a excel template with all the above sheets(Summary, Sheet2 & Sheet3) and KNIME writes the data into respective 2 sheets and summary pics the values using excel formulas automatically and save the file as with variable created with in the loop. Not sure how this can be achieved.
you can provide your comments/suggestions whether my approach is in the right way or any change is required.
If the Excel file has never been created (or it needs to be created from scratch) then you need to use the Excel writer the first time. Make sure that overwrite option is set in the Excel Writer node if this is fine with you.
If the Excel file already exists, then using an Excel Sheet appender as first Excel node is also fine.
Once the Excel file exists, you can append sheets, but it is wise to make sure that they are written sequentially to avoid unexpected results or even prevent the workflow to crash.
In your workflow, execution of the three Excel nodes can happen in any order (even the first one " missing LEQR file" could run after the supposed last one “write to Sheet3”).
To make it to be sequential, a “directed” path needs exist to define a relation of order, for instance as shown in the amended image below:
Besides this, there is perhaps an issue with your first “row filter” node that is connected at the same time from inside and outside the loop start. Not sure whether this is a second issue in your case with respect to the results you want eventually to obtain.
Thanks for your time and input, with little modification as suggested by you, i was able to write the data to sheet2 and sheet3 as desired( loop through).
Now the challenge is how to create the summary sheet from both the data sheets as mentioned in my 1st screen shot as it involves multiple excel formulas and 2 charts/graphs.
I have taken the approach of writing the data to respective sheets of a fixed file (name), then try save as the file with the output of create file name node variable, used merge variable node and looped it. Thought it is no errors, the files are not created, however output of the variable loop end shows as all the files are created, not sure where i am missing or is this correct way of doing it.
Could you post your workflow (with non confidential data) ? This would make easier for people to help you.
I believe that providing a global solution to your request is more involved than it looks like, specially because of the final format of your Excel files that contains images and different tables. You most probably will need to have a look at Continental nodes for the formating :
My aim is to create multiple files based on the create file name node with all the 3 sheets (summary, p2p queries and t&e queries) each.
Since the creation of summary sheet is complicated in KNIME, i have created a template with the excel where KNIME writes the data into P2P Queries and T&E Queries sheets and Summary sheet populate the required data and graph automatically using excel formulas. This template should be saved as with the variable created by the create file name node. This should repeat for all the files.
Hope this helps in understand the requirement better.
If I correctly understand your problem you have an excel file like a template for your report Template.xlsx (14.4 KB) and an excel file with some data Data.xlsx (8.8 KB) to process in KNIME. Then the result is written back to the template for every group (in my example users) so you need n files (based on the template) one for each group (User1.xlsx (11.6 KB), User2.xlsx (11.7 KB) and User3.xlsx (11.7 KB).
I have started with making n copies of the template file (one for each group) and then copy the manipulated data into each file (created before) separately as a new sheet based on user and filename.
If I got you right you were able to populate 2 sheets for each row and if summary sheet is populated automatically what is troubling you?
As a side node I find your workflow example a bit complicated to figure out. Perhaps next time try with fewer node and simple logic or a step by step approach with multiple topics (it is more topics for same issue but sometimes it is easier). Also you can add your data into workflow folder and use relative paths so reader nodes don’t have to be configured
I have the data to process in KNIME. Once it is processed, based on the variable created from create file name node, if the document type is Travel & Expenses, then write the data to T&E Sheet, otherwise write it to P2P Queries sheet. From these 2 sheets, i try to get the volume, value and average using excel formulas and then create graphs out of these numbers.
Then make a copy of the template file and rename (save as in excel term) with the variable.
Similarly all the files has to be created as per the number of users.
I am using the template, so that the summary sheet is updated automatic.
I don’t see the files created in the output folder, i am not sure where i am missing on this.
I have arranged the workflow step by step with annotation to have better understanding and will follow the same going forward, thanks for sharing the best practice. Since i did not reset the workflow while exporting and i thought other can see the workflow the way i see.
i am thankful to folks who are trying to understand and provide solutions as much as they can, i do try to put across the best way to make it more easy for others to grasp/know. I am sure we are very close and crack it soon.
can it be that flow variables determining location are not well defined or configured? Don’t think I can help you more unless you share workflow that reproduces this with some (dummy) data…
!
