How can the data of every three columns be combined to generate new columns

Hi Knime experts
I have data as below:
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
10 12 20 5 11 13 1 6 11 7 8 3

I want this output:
Jan Feb Mar Q1 Apr May Jun Q2 Jul Aug Sep Q3 Oct Nov Dec Q4
10 12 20 42 11 13 1 25 1 6 11 18 7 8 3 18

Thanks in advance.

Hi @Chenfang

You can go for 4 ColumnAggregators (one for every Q); using SUM in the options tab.

gr. Hans

1 Like

Hi HansS
Thanks for your reply. Would you pls show me how to set up .

Hello @Chenfang ,
If you just have 12 input columns then you can also use Column expressions.




Column Expressions


if (regexMatcher(column("column1"),"[a-zA-Z]{3}"))

If you want the order to change , you can also use a column resorter node

Hope this helps


I’m not @HansS but I can help. Configure your first Column Aggregator like this:

After you add the four Column Aggregators, you will also probably want a Column Resorter as well.


Hi HansS
I just understand what you said, but actually I have total 39 months data need to be sum into 13 Q. If any other node to quickly get the result.

1 Like

Hi ScottF
Thank you for your kindly help. But actually I have total 39 months data need to be sum into 13 Q. If any other node to quickly get the result.

Whether you go the Column Aggregator or Column Expressions route, I think you will just have to use either multiple nodes or expressions, respectively. There’s not a single node/expression to get the format you need.


Hi berserkersap
Thank you so much! It’s very perfect solution. I have got the result what I needed.


Hello Chenfang,

Did you solve this by creating 13Q column expressions ?

Hi berserkersap
Yes, I use your method by creating 13Q column expressions which has solve my problem.
Thanks again.

1 Like

Honestly It would have been better if we were able to keep all the Jan values in one column and Feb Values in one column and so on. Then 4Q would have been enough.

was there a demo dataset uploaded. Wanted to try another idea

Hi Daniel
I could share demo dataset, but I don’t know how to upload to share to you.

Hi berserkersap
Thanks! Your original solution is what I want. But I don’t want only 4Q due to I have different year .

Hi @Chenfang , if you used @berserkersap 's idea/solution, then perhaps you should mark @berserkersap 's post as solution, not your post :slight_smile:

I’ve created something more dynamic that will create Qx (Q1, Q2, … Qn) dynamically at every 3 columns, and each of the Q columns contains the sum of the 3 columns, so it does not matter if your table has 3 columns or 39 columns or 300 columns, it will work the same way. It also does not matter what the column names are, it will work the same way.

This is what the workflow looks like:

Input data of 12 columns (same as in your original post):


And now I plug another sample data to the workflow:

This sample data has 39 columns with different names from my previous sample:

Results (it added 14 Q columns, from Q1 to Q14):

I did not need to change anything in the workflow. Just the data changed, and it worked the same for both cases.

Here’s the workflow: Create Q columns every 3 columns containing their sum.knwf (37.4 KB)

1 Like

Hi bruno29a
Thanks for your kindly help and your workflow, I will learn from it.
I am new to Knime. Sorry, I don’t know how to @berserkersap 's post as solution. Would you pls advise.

Hi @Chenfang , I’m not sure how it works for choosing a solution as I have never created a topic/thread, so I’ve not seen the interface for it. But I see that you have figured it out already :slight_smile:

Also, just a correcting to my last post for the last demo (39 columns), the results was that it added 13 Q columns, from Q1 to Q13, and not 14 Q columns as I wrote. It was past 1am when I wrote that :rofl:


This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.