RunningTotal.knwf (6.7 KB)
I’ve created a simple example to illustrate a frustration I’ve encountered using the Labs Column Expressions node to perform a multi-row expression
In this case I’m just creating a running total, in which the result is the current value of a Counter column plus the Running Total from the previous row
In Alteryx this is a doddle using the Multi-Row Formula Tool which, crucially, exposes access to the newly created Running Total field within the tool itself. I can therefore create a simple expression that references the new field that I’m creating, see below
I haven’t figured out a way in KNIME to reference the newly created output column, so I’ve had to resort to a slightly fiddly intermediate variable to keep track of my desired total. Please tell me which node is better suited to multi-row calculations so that I can reference the new column as part of running the node. Thanks
if you need to compute a cumulative sum, there is a node in KNIME called moving aggregation that does what you are looking for.
Have a look at this workflow for settings details:
Thanks @lelloba . The challenge here isn’t to create a running total, which is fairly easy. Rather, I chose that example to ask how to expose a newly created column in Column Expressions. I can’t see a way to access the new column in the column list.
By contract, Alteryx lets you access and manipulate the newly created column. I’m looking to do more complicated things than just a cumulative total, for example multiplying the value in the current row by row-1, or dividing it by another row. And then having KNIME work its way down row by row, with each row iteration using the new column created in the previous iteration. Is that possible? Thanks
You need to use a subsequent Column Expressions node to access the created column at this point. Unfortunately, the formulas do not operate iteratively and build upon each other. I am sure that there is an existing ticket for this change, as many of us (including me) have requested this functionality for the next re-build.
It is a real workflow clutterer to have to use a long series of column expressions nodes (as well as the Rule / Manipulation nodes) instead of just using a single node with a clean list of expressions that can be re-ordered and easily adjusted. It is the least friendly part of the platform in my book. I really dislike the process of constantly having to open 10 Column Expressions nodes in a row trying to locate a specific formula for editing…
Sorry @J_Knime_Work, I should have read more carefully.
Regarding this:
I have no solution. As far as I know, you can act on the newly created column as an output of the node, not inside it.
I cannot picture your needs clearly, but this is something you could potentially but not necessarily do with loops or with a script in Python, R or Java.
There is a guide made by KNIME called “Fron Alteryx to KNIME”, and at page 23 it says something that can help.
