Repeating Task with Math Formula (Multi Column)

[badger101]

Hi, I have a multi-column table like the one below, with a total column of 57 units altogether. (Please ignore why the name of the columns are Rows - that’s simply due to the previous node being a Transpose Node).

I am using the Math Formula (Multi column) to substract the values of all other columns from the first column’s values. I don’t have a problem with this. But I also need to repeat this with Column 2 and so on because I want to calculate the difference amongst all columns with one another. Is there a quick way to do this repetition (for all 57 columns)?

Thank you!

[Kathrin]

Hi @badger101 ,

here is a little example how you can do so using a loop

Forum_Question_Substracting_Columns_Pairwise – kathrin

Best
Kathrin

PS: The Column Rename nodes are necessary, because you can’t use the flow variable “Current Column” in the Math Formula node.

[badger101]

Thank you @kathrin , I’m looking at the Column Rename Node in the workflow you made, and I see that the configuration is set to change Column 1 to temp. But the output when I right-click and choose the Renamed-Retyped table showed that Column 3 is being renamed instead. That confuses me a bit.

[Kathrin]

In the column rename node I use a Flow Variable to overwrite the setting option of the column I want to rename. This means in the first iteration I rename Column 1 to temp, in the second iteration Column 2 to temp, in the third iteration Column 3.

Are you already familiar with Flow Variables and Loops?
If not you might find these two sections of the self-paced course helpful:
https://www.knime.com/self-paced-course/l2-dw-knime-analytics-platform-for-data-wranglers-advanced/lesson3
https://www.knime.com/self-paced-course/l2-dw-knime-analytics-platform-for-data-wranglers-advanced/lesson4

[badger101]

Thanks again @Kathrin . I am not familiar with the loop and variable nodes in Knime, I guess I have to read more. So far, the only experience I have had with success, doing it on my own without any reference, is when I had to (on another sub-project) add a constant value to the constant value node using a variable. It was a simple task

Anyway, I have copy and pasted my table of 57 columns onto your workflow. To summarize, thanks to your workflow, I finally worked it out. It wasn’t a straightforward solution once I applied my columns though. But I will mark your previous post as the Solution. Really appreciate you helping out.

On another note, I will note down here the problems I encountered and how I worked around it, so that other people who might experience similar issues can refer to and act accordingly:

1. I ran the workflow once I applied my columns to the workflow. After the loop runs ended, I checked the results of the math calculation to see if they were correct, and to my surprise they were all positive values, when there should be negative values too. So, I added one more step to the workflow to ensure all raw values were in the form of numbers and not strings (as shown in the screenshot below) by adding a String-to-Number node after the table creator.

2. Once I’ve done that, I noticed a difference in the configuration of the Column Rename with that of yours, where yours had the option to choose Integer in the dropdown menu, while mine had the 6 types of menu items as you can see in the photo above. I chose Double Value in the first place, then I ran the workflow and recheck the calculations. The solutions were wrong again. Finally, I changed the option to either ComplexNumber or FuzzyNumber. The workflow was ran again. Finally, everything falls into place.

I don’t understand what I did myself, but hey, the numbers are now correct (finally!).

Thank you again. It saved my time.