Math Function in Loop

KNIME Analytics Platform
1

I have a dataset where i want column1 to be multiplied with column 2 and output the results in a new column. like wise i want to repeat the operation and multiple column 1 to column 3 to column 20 and output the results in a new column.

I tried using a column list loop start- math node- loop end(column append) but that either performs the operation for the first column(2) or the last column(20). Is there a better way to pefrom the operation instead of a custom R script.

 

Thanks

 

2

Hi Simon,

Do you have an example workflow that i can reference for the exmaple you have cited above. I have not been too successful in creating variables and passing varibales to connected nodes yet.

Apprecieate your help.

 

Thanks

3

I have just modified my original post as it didnt work, try what I describe above. I hope it works for you. And just altered it a second time, when I reread your post, as I initially misunderstood it.

Simon.

4

Hi,

To do this you need the following sequence of nodes and configurations;

Column List Loop Start - Select the desired columns to be looped over (i.e. Columns 2 to 20)

Column Rename (RegEx) - Search string box needs to be defined by a variable, so goto the flow variables tab and next to the Search String text, choose CurrentColumnName from the dropdown. This variable provides each column name on each loop. For Replacement name, enter any name, i.e. MathsColumn.

Maths Formula Node - Enter in MathsColumn*Column1, choose Replace Column, and select MathsColumn.

Column Rename (RegEx) - In Search String box, enter MathsColumn, and for replacement name box, we want the original column name back, so go to the flow variables tab, and this time next to the ReplaceString text, choose CurrentColumnName from the dropdown.

Loop End (Column Append)

All columns 2 through to 20 will now have been multipled by the column1 value.

 

A slight problem with this approach is you will get some of the columns duplicated in the output. i.e. any unlooped columns (like Column1) will appear many times in the output. You may wish to put in a Column Filter just before the Loop End node, choosing Force Exclusion option in the node, and leaving just the newly calculated column in the righthand include list.

If you want those filtered columns back, then after the Loop End node, you can use a Joiner node, and join back from the table before the Loop Start node.

Good Luck.

simon.

5

Hi Simon,

Thanks for the sequence of nodes and the detailed explanation. It works and saves me a ton of time and i get a better understanding of the flow variable tab as well.

 

Thanks

Ramya