Dynamic row calculation of columns

[aj05]

Hi,

I need to make the following calculation in Knime:

I tried with Moving Aggregation and Lag Column, but the thing is that I cannot do the multiplication within the moving aggregation. Can anyone help with this? Thanks.

[iCFO]

Can you upload a workflow with some sample data in it? It is also hard to tell what columns you are referencing in your formulas. What are the column names for “F” and “G”? Is row 2 in the formula the 1st row with data (aka Number = 6)?

[aj05]

Not sure how to edit the post, but here is the screenshot with the columns:

[iCFO]

Super confusing to my eye… All of the cells referenced in the formula appear to be blank.

It will be much easier to see this in KNIME. You can get an upload friendly workflow file by going to File → Export KNIME Workflow… Then make sure that Reset workflow is not checked when you save it.

[aj05]

I’m not really sure what you mean… The first table is how the data looks now, and the second table is what the outcome should be. The column “formula” just shows which formulas have been used for each row in column G.

But I have attached the workflow with the data now.

data.knwf (6.7 KB)

[gonhaddock]

Hello @aj05 and welcome to the KNIME forum.
This is your workflow:

1. Send your column $F$ to variable $${DF}$$
   It takes the first row by default

2. Rule Engine column $factor$:

NOT $F$ = $${DF}$$ => $F$
TRUE => 1

3. Moving Aggregation $Product$
   You can control the window length as variable from a ‘Table Dimension’ node (not included)
   

   

4. Math Formula:

$${DF}$$ * $Product(factor)$

BR

[Daniel_Weikert]

First thought of a lag column node but I like @gonhaddock great solution. MA Node for the win.

[iCFO]

I had started a lag column solution when this one popped up. Very cool approach. I am going to start looking for opportunities to use the Moving Aggregation node more often.