I would like to calculate elegantly the differences of Revenue - Revenue_initial, COGS - COGS_initial, etc.
I tried to use the math formula node (multi columns) but was not able to succeed. You can create a set of current columns, but only do one operation for the selected current columns, e.g. dividing all current columns by 10 or so. What I want to do is to have a list of current columns (namely all columns without “_initial”) and subtract these columns from another list of columns (all columns with “_initial”).
I tried to create a list of the “_initial” columns and add them as flow variable to the math formula, but “list variables” are not recognized. Any idea how to solve this problem the “KNIME low code way”? (No python, R and so on). Is this a feature request to add lists to the math formula universe? I was a bit surprised that I now struggled with this rather easy problem (But maybe I am missing here something?)
I don’t think the Math Formula (Multi Column) supports pairwise operations, but we can trick the normal Math Formula into doing it instead.
The idea is to iterate over the column pairs one by one, so we can assign static names to the columns.
Get a list of column names. Since we only need one entry/pair, we filter out “duplicates”
Iterate over the column pairs. A Column Filter with RegEx configuration gets us a new column pair in each iteration.
Rename columns to static names
Actual processing. Math Formula node is set up with static configuration.
Rename columns back. Would also work with a normal Column Rename
Collect the columns afterwards
Reorder the columns. There’s also the Reference Column Resorter, but it requires a different input (=more nodes) and the Table Validator (Reference) node can do a lot of other things as well.
Quite spectacular! Thank you very much, it works like a charme!
You basically built the algorithm for this array operation Let’s see, maybe KNIME will implement this as a native feature as well, e.g., in the math formula nodes / column expressions.
