Math Formula (Multi Column)

Hello,

I would like to ask you how to calculate some basic mathematic operation for more than one column.

Example:
need to calculate following columns:
Split1Split2
Split7
Split8
Split10*Split11
etc.

I can use Math Formula nodes, but in this case I have to use it many times separately and that is not convinced or use Math Formula (Multi Column). Am I right with Math Formula (Multi Column)?

But I tried a lot of approaches to configure Math Formula (Multi Column), but no success. Can someone help me, how to solve this? Or at least give me some hint. Or maybe totally different solution.

Thank you

Hi,
for this I think the Column Expressions is the simplest solution, because it allows you to access column values by index. You can use Double or Integer as output type, check the box for collection, and then use the following expression if you have at least two columns:

var a = arrayCreate(column(0) * column(1))
for (var i = 2; i < columnNames().length; i += 2) {
    a = arrayAdd(a, column(i) * column(i + 1))
}
a

The resulting collection column can then be turned into multiple columns using Split Collection Column.
Kind regards,
Alexander

3 Likes

Thank you Alex. I tried and it works, but I have to manually specify column index where to start and it makes multiplying of two neighboring columns till the end of the table.

Am I right?

Do I understand correctly how this expression works?

Thank you

Hi,
yes, this is ok. I just assumed you want to do it for all columns. Otherwise you need to adapt the expression a bit in the first two lines.
Kind regards,
Alex

1 Like

No worries, but now there pops up a performance question.

Which solution would be quicker? This Column Expression node or more Math Formula Nodes in chain?

Thanks

Hi,
Math Formula nodes are in general faster than the Column Expression node, but can only produce a column at a time. Maybe you could try multiple Math Formula nodes in a streaming component. If you are familiar with Java, you could also use a Java Snippet, which is quite fast as well.
Kind regards,
Alex

1 Like

oh right now the combination of Math Formula nodes in a streaming component looks quite difficult for me and unfortunately I am not good in programming at all. That is the reason why I use ETL tools… but thank you for your help. I really appreciate it!

Hi,
I think the streaming solution is quite simple, you should not be afraid to use it :smiley:. You just need to build the chain of Math Formulas like you planned, then wrap them in a component and in the component’s configuration you change the executor to the streaming executor. All you need is the KNIME Streaming Execution (Beta) extension.
Kind regards,
Alex

1 Like

ok, i try to use and let’s see!