Math Formula Multi Column considering column headers

Dear Forum users,

I would like to calculate a portion for each row dependent on the column names. Here is what I have:

So I would like to calculate column AED / AED (#1), EUR / EUR (#1), etc,

For the Node my expression must be something like:

$$CURRENT_COLUMN$$ / column($$CURRENT_COLUMN$$ &" (#1)")

As involved currencies may change every month I cannot work fixed columns. As number of involved currencies can also change I have no idea how to use column indices.

I also tried using Column Expressions Node, but was not successful finding the correct expression for my calculation.

Any ideas?

I would first sort the columns, then use a loop to always choose 2 columns eg. EUR EUR(#1) and then rename then with “dummy” names so the math formula afterwards can always divided column A / colum B. Then rename then to the original names back.
br

4 Likes

I like Daniel’s suggestion. To set up the loop automatically, you might need regex (in particular the column filtering nodes using regex seem most useful here).

If the currencies always follow the 3 letter standard and there are no other columns with 3 letters, then you can filter to only those columns as reference and trigger loops/regex to iterate through that filtered list capturing the other #1 pair you’re looking for.

3 Likes

Dear Knime friends,
many thanks for your suggestions. I tried the Loop-way from Daniel and also implemented Victors ideas with the regex - it works fine for me.

Many thanks for your help

1 Like