Adding rows to next row in a table

[cevenerdem]

Hi, I am a new bee.
I would like to do a calculation as explained in the following picture.

For every row of the same value in ColA:
Same rows of columns ColE, ColD will be added up, and ColC will be subtracted from the sum (yellow highlighted). The outcome will be stored in the following row of ColD (pink highlighted).

In each row for the same ColA value, the first rows for ColD will be 0.

Any help highly appreciated.

[ipazin]

Hello @cevenerdem,

welcome to KNIME Community!

From my experience going scripting is the way for this one. (Not that it couldn’t be done using KNIME “manipulation” nodes but just feels better to write some ~10 lines of code to do it). For example you can use Column Expressions node and use it inside Group Loop or use it with loop logic integrated inside.

Column Expressions is based on JavaScript but code is pretty straightforward so shouldn’t be a problem to figure it out. In case you have questions don’t hesitate to ask! Here is workflow on KNIME Hub which you can download and use.

Excel Formula in Column Expressions – ipazin

Br,
Ivan

[Luca_Italy]

Hi Ivan,
can you explain me how that JS script works?

case 1 and case 2 are pretty straightforwarded, but i don’t really catch the default case:
where you define the result? aren’t you only declaring a variable there?

sorry but first time JS here

[Daniel_Weikert]

Can’t you just shift ColE and then sum the rows up?

[ipazin]

Hello @Luca_Italy,

@Luca_Italy don’t need to apologize. Not a JS expert so not sure if JS works like this but simply speaking Column Expressions node outputs last row value encountered. For example if you just write following expressions will get value 2 for each row:

sum = 1+ 1

Which is actually same as if you write

1+ 1

or

sum = 1+ 1;
sum;

@Daniel_Weikert not so sure it is that easy but you are free to give it a go

Br,
Ivan

[Daniel_Weikert]

Can you provide a sample file (excel,csv)?

[ipazin]

Hello @Daniel_Weikert,

you can use dummy data in Table Creator node in linked example above.

Br,
Ivan

[Daniel_Weikert]

Thanks Ivan,
it works but requires more nodes. I think you solution is better here.

By the way. Is there a documentation giving info about computation times for nodes. It might be the case that certain workflows with more nodes could be still better then lesser but much more computational expensive nodes (e.g. Transpose is not a good thing to use as far as I know)?

[ScottF]

There is not documentation on this per se, but you can use the Timer Info node if you want to see how long particular nodes take to run.

[ipazin]

Hello @Daniel_Weikert,

not bad

And you are right, less nodes doesn’t always mean faster execution.

Br,
Ivan

[cevenerdem]

Hi Ivan,

Thanks for the help.

It works.

Another way, which I implemented, is the use of a python code to do the calculations.

Rgds