Hi,
Traying to figure out:
Having data 123456789… in which each number is in a different column,
I want to insert a new column with a comma regularly after 3 values. Like:
123,456,789,…
Any ideas?
Thanks…!
Hi @VAGR_ISK
another solution would be a “String Manipulation” Node.
If your DataType is string already:
join($column1$+$column2$+$column3$+","+$column4$+$column5$+$column6$+","+$column7$+$column8$+$column9$)
If your DataType is not a string already, either use a e.g. “Number to String” node before the “String Manipulator” or it should look like this:
join(string($column1$)+string($column2$)+string($column3$)+","+string($column4$)+…
5 Likes
Hi @VAGR_ISK
Another idea is to Transpose your dataset (if it is not that big 
) and do some Looping
insert_column.knwf (39.0 KB) . That is what KNIME makes fun to work with, multiple ways to find a solution.
gr. Hans
4 Likes
True by any means, there are so many ways in Knime!
“not that big” 
made my day 
3 Likes
You could also use regex (Yeah I know many ways lead to Rome)
I used the column aggregator to concatenate the columns first
for the split I used
(.{5})(.{6})(.*)
and because I had to remove the commas from second and third split I used regex to remove “,” from the start and end of the string.
br
3 Likes
Just a note to everyone, I’ve not looked at all the proposed solutions, but anything using the join() function will work only if it is guaranteed that all 9 columns have a value, which I don’t know at this point - it’s not confirmed by the requester.
If there are any missing values, Knime will not join with missing values. For example:
Of course, you can always convert missing values to empty string:
toEmpty($$CURRENTCOLUMN$$) via a String Manipulation (Multi Column)
And you are able to then join:
On the other hand, Column Combiner (as well as Column Aggregator used by @Daniel_Weikert) would combine missing values:
May be going with the Column Combiner/Column Aggregator + a regexReplace() to remove all commas except every 3rd one would be the fastest, unfortunately I’m not quite good with regular expression to come up with the proper expression for that.
1 Like
Ok, I ended up doing it the other way, that is concatenate the columns with NO separator, which yields 123456789, then adding commas at every 3rd position. I was able to get the proper regex for that:
regexReplace($Concatenate$, ".{3}", "$0,")
Results:
Workflow looks like this:
Here’s the workflow:
Insert new column with commas.knwf (8.3 KB)
Note: A lot of the proposed solutions here, including mine, have hardcoded 9 columns, and these solutions work only for 9 columns. Re-reading the requester’s post: “I want to insert a new column with a comma regularly after 3 values” and also notice the three dots (…) in the input data and in the result, these would suggest that 9 columns was just an example. If we have various number of columns, a lot of the suggested solutions would have to be re-configured.
Probably @HansS solution would be the most flexible to configure (change chunk size, which could also be controlled by a variable)
6 Likes
Hi @bruno29a,
Using the regex replace ($Concatenate$, “.{3}”, “$0,”) works super, but you also give me the empty ($$CURRENTCOLUMN$$) for empty columns that were killing me, that was also a question that I was looking for quite some time ;).
1 Like
Hi @VAGR_ISK , you can also play around with this flag to include the missing values or not in the Column Aggregator:
2 Likes
Hello there,
nice to see so many different ideas and solutions!
@bruno29a yours is easily generalized with adding another regexReplace() as wrapper function:
regexReplace( regexReplace( $Concatenate$, ".{3}", "$0,"), "(.*),$" , "$0" )
Br,
Ivan
6 Likes
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.