# Appending calculated rows in a table problem

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

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

3 Likes

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.

1 Like

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.

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:

1. 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.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.