Use Math node to calculate value from columns that have similar headers

[TigerCole]

Hi,

I need to do some math on the table below

I need to calculate a value that would be (Column 2021-11) / (Column 2021-11 (total)) for each of the columns that has a matching *(total) column.

Does anyone possibly have a solution for this?

tC/.

[aworker]

Hi @TigerCole

Could you please share the data in csv format ? I could thus suggest a workflow solution without loops from there. Thanks.

Best

Ael

[TigerCole]

Hi @aworker

I have attached the XLSX file. Thanks for your help.

SampleFile.xlsx (4.2 KB)

tC/.

[aworker]

Hi @TigerCole

Thanks for the data. Please find below a workflow which does the division based on a -GroupBy- node:

20220307 Pikairos Use Math node to calculate value from columns that have similar headers.knwf (64.1 KB)

Edit: If you need the “CheckId” column too, the only thing to change in the current workflow is the regular expression in the -Column Splitter- node as follows: ^.+total.*

Hope it helps.

Best

Ael

[Daniel_Weikert]

@aworker hat is really nice. I was immediately on the “nobody likes loops” trip to solve this.Thanks for sharing

[TigerCole]

Hi @aworker … very nice solution. Worked like a charm.

I just need to figure out the regex so that I can apply it again on a new dataset.