Math operations by referencing columns

Hello Team,

Does anyone know how one could do operations by referencing columns in KNIME?
My goal is to achieve the below scenario

current column(row i) = (Sum of Previous 5 columns from this table(row i)/(Sum of Previous 5 columns from 2nd table with same column name(row i)*(Current column from the 2nd table(row i)

(*Both tables will have same column names)
ColumReference.xlsx (15.3 KB)

I have attached the case in the below excel file

Thank you in advance.

Best regards,
Sahil

Hi @Sahil1,
I have attached an example workflow that shows how you could tackle this problem. There are no sliding window operations over columns in KNIME so I used a Chunk Loop Start node to work a row at a time and transposed your table to make use of the Moving Aggregation node.

Reference Columns math.knwf (43.4 KB)

best,
Gabriel

1 Like

Hi Gabriel,

Thank you so much, this is exactly I was looking at :slight_smile:

You guys rock!

Best regards,

Sahil

1 Like

Hi Gabriel,

I just realized a problem, I would like the calculation only for the columns ending with Est in table 1. Other Act columns must be retained with old data. Could you suggest me a way.

Thank you in advance.

Best regards,
Sahil

Just use a Column Splitter node to split off the desired columns, you can join the back together using the Joiner node.

best,
Gabriel

Yeah, I was doing the same. But was looking at a more optimized way :smiley:
My problem is solved, so no issues
Thank you Gabriel :slight_smile:

Hi Gabriel,

I received a case where few cells are empty, and I am not able to adapt your logic. I tried many ways but its not working. Could you check.

Problem,
I have 2 tables: when the first table has value, the output should be directly from the 1st table otherwise the output should be calculated by taking the second table.
The formula is
'IF(ISBLANK(Table1 column value),((SUM(sumof previous 5 columns from table 1)/sumof previous 5 columns from table 2)*(current column from Table 2)),Table1 column value)

Please find the attached excel for details.
Thanks in advance,

ColumReference.xlsx (16.4 KB)

Best regards,
Sahil