Updating variable and table in a loop

[takbb]

You’re welcome @badger101 , and quite right that you should mark @ActionAndi 's response as the solution, as it is the specific answer to your question; I was simply adding alternatives.

Re your questions:

@badger101

1. I’ll start by saying the one-node CE(legacy) solution is super impressive. As a non-coder, this is my first time seeing the use of “var” script in the first two lines. I wonder how I had missed that functionality all this time using Knime. It’s very helpful since it involves some kind of variables without using the Table Row to Variable Node, never knew something like that can exist!

The “var” statement does create an internal variable (not a KNIME flow variable) just within the Column Expressons script. It isn’t strictly necessary in most CE scripts, but it’s presence here tells the CE that the variable exists which is important for this type of calculation, specifically because at line 9, the variable “prevInitials” is referenced in the calculation, but it’s value is only being set at line 12. If we didn’t tell CE that the variable existed at the beginning, this would throw a syntax error. A quirk of CE (and similar for java snippet) is that a variable defined in this way retains its value from what it was when processing the previous row, and we are making use of this “hack/feature” to make this calculation work.

@badger101

Is the command “var” used to create (or define) the variable, followed by the column name as the source? Let’s say if I have a hypothetical column named X with two rows of double values, if I type in “var X” (without the quotes), what shall the outcome be?

No the string used with the var command is unrelated to any KNIME Column or Variable names. It is simply an arbitrary name used as a “CE variable”. Column Expressions scripts are based on javascript, so in effect this is a javascript variable.

In the above code, I could have written it as var Badger, and var Takbb. It wouldn’t have been quite so easy to understand, but it would still work (I’ve not tested the below but in principle its right!)

var Badger
var Takbb

if (Takbb== null)
{
    Takbb=column("initials")
    Badger=0
}
else
{
    Takbb=(column("constant A") * Badger+ column("column2") ) / column("constant B") 
}

Badger=Takbb
Takbb

The important thing in making this work, is that at the very end, there is a line which stores away the current value into a variable so that it is picked up on the next row iteration:
prevInitials=currInitials

(or Badger=Takbb )

more info on how this works for this type of calculation in both Java Snippets and Column Expressions can be found in my “knowledge sharing” posts on this topic:

Java Snippets Have Long Memories

Column Expressions Have Long Memories Too

@badger101

Regarding the Math Formula Node, I never knew that we can write an if-else script inside it Also the script that you wrote did not have an “else” statements or “else if”, but it still worked wonders. I’m curious as to how the Math Formula Node can distinguish if-else statements simply by using commas like you did there.

The if-then-else syntax of Math Formula is not often seen, but it is there, and is actually similar to the if-then-else syntax of the new Expression node in KNIME 5.3 onwards. It is also similar to the Excel if(condition, value-if-true, value-if-false) syntax. It doesn’t contain keywords then or else but is simply a function which takes arguments for the then value and the else value.

So this statement:

if($${IcurrentIteration}$$==0,
 $initials$,
 ($constant A$ * $PREV#initials$ +$column2$) / $constant B$
)

reads as:
if currentiteration equals 0
then use value of $initial$
else use value of ($constant A$ * $PREV#initials$ +$column2$) / $constant B$

Finally, if you are at all interested in using recursive loops for such calculations and possibly making use of the “cumulative framework” components, see the following:

A workflow template for Cumulative Calculations