Using data from previous row in calculation with loop

Hi! I can’t find a way to calculate this yellow column in KNIME, the blue cells are a simple math formula but I don’t know how to tell KNIME to only do that math formula for the row with the earliest date for each product and the red cells depend on the value above them so I’m guessing it needs some sort of loop but again don’t know how to do it just for the dates after the earliest one for each product.

Does anyone know an easier way to do this? what I need is the weekly balance, starting with the initial stock.

Hi @Aura02 , I was confused about the “formula” column included in your screenshot that makes no sense to me, so I ignored it, and just worked on the basis that

Balance = (initial stock if first row for product, or previous Balance otherwise) - Client orders + incoming stock

This doesn’t require loops, and can be achieved using Column Expressions formula:

/* Items to "remember" for next row -- do not initialise them */
var lastProduct
var lastBalance

// processing for the current row...

if (column("Product") != lastProduct )
{
// this is the first row for this product
    lastBalance = column("Initial Stock") // initialise lastBalance to initial Stock Level
    lastProduct = column("Product") // remember the lastProduct
}


lastBalance = lastBalance - column("Client orders") +column("Incoming Stock")

// return the balance for current row
lastBalance

Cumulative Stock Calculation.knwf (73.0 KB)

for info on how this works, see

3 Likes

About a week or so prior to writing that post on Column Expressions, I was trying to find a “boilerplate” solution for cumulative calculations, and I built what I called the “cumulative framework”. This consists of a component which you handles all of the “previous row value” stuff on your behalf and hides all the functionality in further components.

As an academic exercise, to see how this “cumulative framework” would resolve your problem, it can be done like this:

What you do is download the Cumulative Framework component from here:

and follow the instructions on the component description.

You need to unlink the component, so that you can open and edit it.

You edit just the Rule Engine and Math Formula nodes. Executing the component up as far as the Rule Engine prior to editing, means that the component will supply simple to use columns for the previous row details like this:

The Column Expressions or Java Snippet equivalent solutions are probably the simplest way to resolve the problem, but for anybody not wanting to write the script, the Cumulative Framework provides a possible solution.

Cumulative Stock Calculation - using components 1.knwf (134 KB)

2 Likes

Hello @Aura02 , and welcome to the KNIME community.

I’ve deployed an alternative solution for your use. This alternative solution is also supported with ‘Column Expressions’ node; however it deals with the problem with very simple math, using by ranges the full cumulative column for each of the variables scoped in the equation. Then no loop is needed, improving in performance.

As @takbb commented, it wasn’t clear for me either the excel formulas coming from the image… then I decided to simplify the equation as:

Weekly Balance = [A] Initial Stock - [B] Client Orders + [C] Incoming Stock

You can work with the full cumulative column for each variable, and individualize the ranges (this is $Product$) accessing with Column Expressions’ Offset function.

Then the equation becomes:

Weekly Balance = [A] - [B] + [C]

  • [A] == column offset $Initial Stock$
  • [B] == cumulative $Client Orders$ (delta range)
  • [C] == cumulative $Incoming Stock$ (delta range)

For example for the [B] variable $Client Orders$ (delta range) in Row5 (‘Jeans’ product range):

The [B Row5] calculation is 5 + 1 + 2 => 51 + (5 - 48) = 8
In the same way [C Row5] calculation is 40 + (5 - 23) = 22
And the outcoming $Weekly Balance$ [Row5]: 10 - 8 + 22 = 24

The Column Expressions’ code is as follow:

// column offset $Initial Stock$
column("Initial Stock", column("ROWINDEX_offset") - column("ROWINDEX"))

// minus cumulative $Client Orders$ (delta range)
-(
    column("Sum(Client Orders)") + 
        ( column("Client Orders", column("ROWINDEX_offset") - column("ROWINDEX"))
            - column("Sum(Client Orders)", column("ROWINDEX_offset") - column("ROWINDEX")) )
)

// plus cumulative $Incoming Stock$ (delta range)
+(
    column("Sum(Incoming Stock)") + 
        column("Incoming Stock", column("ROWINDEX_offset") - column("ROWINDEX"))
            - column("Sum(Incoming Stock)", column("ROWINDEX_offset") - column("ROWINDEX"))
)

Any comment or clarification request is welcome.

BR

3 Likes