How to average row data using column header

Hi.

I would like to create a formula that averages data from multiple columns based on the header description. Specifically, I want Knime to automatically create a column that has the average rate for a quarter, half year, and year for each based on the dates which are also my column names (see screen print below)

I understand I can manually create Groups but am looking for something that will automatically create the groups based on the date range of the input files.

I assume this is something that can be easily done but am struggling to find the solution. so any help would be appreciated.

Thanks.

node could be useful but still parameters for grouping needs to be specified manually.

2 Likes

Hi there @WilsonR,

hmmmm. You can use Column Aggregator for each column group which row values you want to average. In Column Aggregator you should use regex to define your groups. For example for Q1 2017 use this regex: 2017-0[123].*, for half year use 2017-0[123456].* and for whole year use 2017.*.

Don’t know for some easier way :thinking:

Hope this helps!

Br,
Iva

1 Like

Hi @WilsonR:

I am a bit confused - are you looking (1) to average within a column aka the column is the group or (2) average multiple columns (eg, col a, col b, col x (group 1) and col g, col q (group 2), etc?

I am looking to average multiple columns (the columns represent periods). and ideally using a logic that will eliminate the need to do subsequent aggregations as new columns (ie, new periods) are loaded to the model

thanks. this helps with the current dataset. but was hoping to find a more automatic solution as the model will be updated with new periods.

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