Im new to the knime, and im just getting to know the advanced stuff of knime. I am trying to split a file based on each row value in a certain column. For example i have this table :
Info1 Info2 Dealer Path of file
32 customer JVC76 C:\Users\JVC76
32 customer JVC76 C:\Users\JVC76
31 private JVC100 C:\users\JVC100
I want to split the file into multiple files using as key the Dealer field, and ideally save it to the path in the next column. Splitting the above table should give me 2 files :
first file :
Info1 Info2 Dealer Path of file
32 customer JVC76 C:\Users\JVC76
32 customer JVC76 C:\Users\JVC76
second file :
Info1 Info2 Dealer Path of file
31 private JVC100 C:\users\JVC100
Notice that i want to keep the header column in each split.
Imagine this result with 300 different Dealer codes. I am trying to use loop and within each loop to write it to an excel. Here are the problems encountered. First i cant "teach" each loop to get split based on the dealer value.And secondly i cant define to each loop the different path. Ia mguessing i should use a code snippet in java ? I only know SQL so its a problem for me.
You can loop through the Dealer column values using the Table Row to Variable Loop Start and one of the Loop End nodes. In the loop you can Row Filter by the actual variable and write the result to the file. You might need a Java Edit Variable to append the variable to the path (and also possibly an extension), something like this:
out_path = "/tmp/" + c_columnName + ".csv";
And use this new variable as the location of the write node.
I'd imagine you could just useGroup Loop Start on the Path of file column and it will loop over the different dealer codes.
For saving to the excel file. Use excel writer node, first select a dummy file to write too. Doesn't matter what it is, it's just a placeholder. Then in the xls writer node config, choose flow variables tab, and in the drop down which corresponds to the write file name choose the current iteration value. This tells the node to write to that file name for each loop .
Thank you for your responses. Actually i couldnt get to work Table row to variable loop start.. Didnt have ane selection as to what column i want the filter.
On the other hand the Group Loop start worked perfectly. The solution for the excel write path was a bit different that you suggested, i actually created it myself on the initial file using the Java snipet from aborg suggestion,so i just used the variable "path" as i named it to save each file in the correct path & name.Quite a nifty trick.
And its fast too. I was using a SAS macro for this job,took it ~20 minutes to run, Knime run it in like 2 minutes !!! Why is it SO much faster ??Thought Sas was the best tool and it costs a fortune to buy (had a 30 day trial)
Thank you guys for the help really got me going !! Is there a mass send mail node or something similar btw ? I am beginning to love knime..I would like to write some code though,to learn,everything is easy so far :)