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! :slight_smile: )
@ArjenEX will be able to proclaim that Column Expressions can achieve whatever needs to be achieved :rofl:

(unless of course you want to do it quickly, in which case… Java Snippets all the way… :wink: )

Some background…

While researching/creating a component to assist users in performing cumulative calculations, without scripting, and trying to find examples and questions that had been posed on the forum to test it with, I came across a post from May 2020 which changed everything. Well… it changed… something…

I will use the example data I gave in a recent post on cumulative calculations:

Here, we have the following table:

image

and the aim of this exercise is to populate ProjectBalance with the running total of Income minus Expense for each Project.

The output I want to produce is this:

So you can see that on the first line, for Project A, the ProjectBalance is 1000 - 0 = 1000. On the next line we subtract 500 from the running total to get 500 and on the third line we subtract 250 from the running total to get 250. At line 4 we add 500 to the running total to get 750 and so on.

When the project changes to Project B, we start the total again at zero and add the income 300 and continue like this until the end.

An accumulation of cumulative processes

My go-to way to resolve this previously was the Java Snippet, making use of its ability to remember variable values from one row to the next as follows:

A solution can also be achieved without scripting, using a recursive loop, but I realised that, like the java snippet, this approach wasn’t necessarily for everybody and I certainly wouldn’t want to build a whole recursive loop workflow every time I needed a simple calculation. So I tried to make a recursive loop process more generic and built the "Cumulative Framework" component(s), referenced in the above post, which buries all the recursive handling and you can just add the Rules/Formulas etc.

image


So the “Cumulative Framework” component means that anybody can perform this type of processing without resorting to any scripting nodes. Slightly fiddly, in its implementation because of limitations of components, but it works.

But… going back to the whole point of this post… there are a lot of people who are happy to do some scripting, but would prefer Column Expressions, rather than Java Snippets. And that’s when I stumbled across the post from May 2020 which showed that Column Expressions actually has a hidden characteristic, which is exactly the same as the way Java Snippet behaves. Forget all the “advanced options” with row-windows etc… it turns out that Column Expressions have Long Memories too.

The Column Expressions node, can remember a value from one row to the next if you use variables in a certain way. If you define variables, but only initialise their value when they are null, (which they are on the first row), but then don’t initialise them after that, then on subsequent rows, they retain their previous value. This is exactly the principle used in the Java Snippet code.

This is the Column Expressions and Java Snippet code side by side. I think you’ll see the similarities:

Here is a workflow containing the above examples, and a couple more, e.g.

Column Expressions Have Long Memories.knwf (137.4 KB)

8 Likes

This is a legitimate monumental breakthrough in my book! I am a heavy user of Column Expressions node, so this will be a game changer for me. A massive thanks for the time and effort you put into this for the community @takbb !

1 Like

CE continues to do me a lot of favors :slight_smile:

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.