Using data from previous row in calculation with loop

[Aura02]

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.

[takbb]

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

Column Expressions have Long Memories too!

OH… WELL THIS IS EMBARASSING! I was wrong about the capabilities of the Column Expressions node. The implication of this is: @iCFO will no longer be pointing people to my Java Snippets (although I always enjoyed the challenge! ) @ArjenEX will be able to proclaim that Column Expressions can achieve whatever needs to be achieved (unless of course you want to do it quickly, in which case… Java Snippets all the way… ) Some background… While researching/creatin…

[takbb]

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:

Cumulative Framework – takbb

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)

A workflow template for cumulative calculations

I am aiming to simplify this further, so that a cumulative calculation can be added to any workflow by the addition of a few components and recursive loops. This is my initial prototype, using 3 “Cumulative Framework” components that work together within a recursive loop. So you simply drop them onto any workflow and join them up in the pattern shown, with zero configuration to provide cumulative calculation functionality. [image] see also this component which encapsulates the above workfl…

[gonhaddock]

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"))
)

Logistics _ Weekly Balance from Ranged Cumulative Equations – gonhaddock

Any comment or clarification request is welcome.

BR