Appending calculated rows in a table problem

[ajit555]

In a typical In financial model, we have:

• financial months as columns
• “data” rows imported from some application.
• “calc” rows which are formula driven.

calculated-rows.xlsx (11.9 KB)

In the attached excel file, in “input” sheet, we have the “data” rows:

We need to append and calculate some “calc” rows as shown in “output” sheet as below:

How to do this in Knime? Any lead or solution would be very helpful.

Regards,

Ajit

[HansS]

Hi @ajit555

Transposing your dataset makes it easier to do the calculations. When done, transpose your data again into the original order, see Appending calculated rows in a table problem.knwf (56.5 KB)

gr. Hans

[ajit555]

Thank you so much for the solution.

In Excel, most of the time while modeling, we are thinking in terms of “Cells”.

So, when we are transitioning from Excel to Knime, we need to think in terms of “Columns” and “Column based calculations”.

In Knime, rows are primiarly used for aggregation and row windows based calculations.

[HansS]

Thanks for verifying my solution. Maybe the Transpose node is not always needed. With the GroupBy and Row Aggregator node in combination with the Concat node you might also find a solution. The challenge here was in the “prevperiod(line7)+line8” calculation.

[ajit555]

Found one compact solution using “Column Expression” Node.

Calculated-Rows-Table.knwf (97.6 KB)

Facing below problems:

1. For calc3 column, first row, I need to convert null value to zero to evalue it to “43” instead of null.

I have requested for some solution here:

Converting null to zero in "Expression" and "Column Expression" node.

Hello, Could you please help in modifying the expression where the null values can be converted to O (zero) in the expression itself (using if … else or anything else) ? Column-Expressions.knwf (77.0 KB) How to convert the null value to zero using expression for the below nodes: “Prev_c1” column, first row, null value should be 0 (zero) [image] “Prev_c1+c2” column, the first row, the value should be 10 instead of null [image] How to compute the same (“Prev_c1” & v"Prev_c1+c2" columns…

2. In the “Column Expression” node, I can not define “calc5” column which uses “calc4” column. I need to define another “Column Expression” node to use the calculated columns.