The objective is to fill up all the missing values in the “initials” column where the neighboring row in the “starting reference row” column is not a missing value too.
The details:
The “starting reference row” are already populated with values, where the starting row is the first non-missing value row. (It always starts with 14 and continues until the last row. This column will be used as reference to fulfil the abovementioned objective.)
I managed to extract the first initials using that info (in this case the first initial is 0.107 – the first initials are always given). Then all I did was fitting it onto the fixed equation inside the Math Formula Node.
The outcome of the calculation is then extracted. This is where I end up with.
I need to reuse the outcome to:
Populate the remaining rows as per the objective.
But in doing so, I will have to update the variable to be used in the equation so that each outcome will be reused to calculate the next row.
Hope this makes sense. I think maybe this is suited for the Recursive Loop Node but I don’t have enough experience in using it, so would appreciate help in solving this. Thanks!
As @ActionAndi says, recursive calcs can take time over larger datasets.
If you are happy to write a short script, you can perform this kind of calculation faster using java snippet or the (now legacy!) Column Expressions node. Unfortunately, to my knowledge, the new “Expressions” node does not (yet?) support this kind of calculation.
Here is the Column Expression that would calculate the required initials value
// Your custom variables:
Double prevInitials=null;
Double currInitials=null;
// Enter your code here:
if (currInitials==null)
{
currInitials=c_initials;
}
else
{
currInitials = ( c_constantA * prevInitials + c_column2 ) / c_constantB;
}
prevInitials=currInitials;
out_initials = currInitials;
If, however, you want to stick with recursive loops because you don’t want to script, but the idea of working out the recursive loop is somewhat daunting, I built a set of components that I call the “cumulative framework”. They are assembled in a standard pattern as shown below, where you don’t configure anything other than the part in the orange box
These components work together to build all the framework that allow you to then write in the calculation that you require, by keeping a copy of the previous row’s column output that you can then reference as PREV#columnname.
For example, the Math Formula node can refer to the previous row’s value for “initial”:
To use the Cumulative Framework, download the following component and follow the instructions in the component’s description. Then fill in the nodes you need in the orange box:
I’ve added examples onto the end of the previous workflow that @ActionAndi supplied:
@ActionAndi Thanks so much for the solution, since I specifically mentioned Recursive Loop, and I had already spent time to integrate your solution to my overall workflow to make it work (before @takbb posted his alternatives), I chose yours as the solution ~!! At least this part of my tool is settled for now, and can move on to a different part
Thank you too to @takbb for providing alternatives. Since I saw new things about some of the nodes I’m familiar with, I would love to ask a few questions to increase my Knime knowledge:
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!
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?
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.
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:
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.
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:
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.
reads as: ifcurrentiteration 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:
So many hidden gems I have missed out on when I was gone! Great work you did in those posts. I will certainly use that one-node solution by CE if my final work turned out to be too node heavy in its final stage later. Many thanks for sharing your knowledge extensively @takbb
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.
)