How to make such a node from alteryx in knime? I need to sum my column (ESP new) up as a cumulative total using conditions.

Hi,

there is no “one node” (beside scripting nodes) because what you show is also a workflow

Have a look at moving aggregation node which can compute cumulative values

br

What does “using conditions” mean?

Thanks, I’ll look at it. May be you know python script to realise this node

If else conditions as on the screen

I think, I need a loop here to realise it but I’m a beginner in knime, so it’s difficult for me to understand working logic of these nodes.

The screenshot is practically impossible to read and without some data its difficult to follow the logic.

@divaice71 you might have to define what you want to do. Maybe best with an example.

Sums and moving aggregations with conditions there were a few examples on the forum.

Hi @divaice71

You can search the forum for “sumif”. There are some responses.

Ex: have a look at

Regards

Hi @divaice71 most users here are unaware of Alteryx and therefore might struggle to understand why analytical steps that are blissfully simple in Alteryx can be far more complicated in KNIME. In this case you need to check out the Column Expressions node which gives access to Row0, Row-1, Row+1 etc, Column Expressions – KNIME Community Hub

In the advanced tab you can configure it for Multi-row access, and I normally use null for rows prior to the first

** Enable multi-row access, window size*

*If selected the script’s column(Object, int) method enables access to column values in preceding or following rows of the current row. The second argument (int offset) thereby defines the number of rows to look “behind” or “ahead”. Since this can be a costly operation (data needs to be cached) a window size needs to be specified. For instance, a value of 5 will cause the node to cache 5 rows prior the current row and 5 rows following the current row. Attempting to access a value outside this window will cause the node to fail.*

Hi @divaice71 , rather than simply pasting a screen shot of an Alteryx expression, it would generally be better to tell us (in non-Alteryx terms) what it is that the Alteryx expression is doing, or at least what you are trying to achieve. You cannot assume that people on this forum can "read ‘Alteryx’ ".

It has been about 3 years or so since I last used Alteryx

I’m assuming that this expression means update the value of “X” as follows:

```
if year of A = year of B
and month of A = month of B
and C = C(previous row value)
and D <> A
then
X + E (previous row value)
else
X
```

which in Column Expressions could be written like this:

```
if (getYear(column("A")) == getYear(column("B"))
&& getMonthOfYear(column("A")) == getMonthOfYear(column("B"))
&& column("C") == column("C", -1)
&& column("D") != column("A")
)
{
column("X") + column("E" , -1)
}
else
{
column("X")
}
```

As has been mentioned, ensure you have set Window Size on the advanced tab so that it can collect previous row value (enable multi row access)

If the logic of my Alteryx translation is not quite right, then hopefully you can at least see how the syntax compares. Column Expressions is based on javascript, so you should be able to find general help with basic syntax on the web.

From what I can see of your formula, the value you are updating is not “self-referencing” from a previous updated value of the same column so this should suffice. If you need to write a Column Expression that can self-reference the previously updated/written value of the column it is updating, then see the following post “Column expressions have long memories too”:

Thanks for your suggestion of solution to the problem. It can be appropriate solution

I’ll take your recommendations into account. Your recommendations helped me to solve my question

Totally agree with @takbb. A Column Expressions node will work in this case because you are not using the result of a calculation from a previous row in the current row. You would have had a problem if any part of the formula referenced [Row-1:ESP new] because while Alteryx calculations operate on a copy of the data that is continually being updated, KNIME calculations operate from a static copy of the input data and updates a new copy, so any results from previous rows can’t be used in the current row within a node. This seems to be true of most of the KNIME nodes I’ve seen with the exception of the Missing node and many of the aggregation nodes. There are a number of ways to get around this depending on exactly what you want to accomplish.

Hi @Bytecrawler, yes that is a limitation of Column Expressions in regular use, and for a long time I thought it couldn’t be achieved at all with Column Expressions, but it turns out that Column Expressions can be coded in a certain way so that a column can self reference the value that was written out for it on the previous row, by storing it in a variable, the same way it can be achieved in Java Snippets. It’s not perfect but it covers many use cases that had previously seemed ‘not possible’.

Examples of how this can be achieved can be found both nodes can be found in the two posts “Java Snippets have long memories” and “Column Expressions have long memories too!” post I referenced above. I hope you find them useful .

Thanks, @takbb. Something was nagging me when I was writing my last reply and it was that I needed to check if variables carried over between rows. This is going to be incredibly useful. I’ll check out your posts. I do wish we had a better way of searching for articles of interest. There have been too many situations where I’ve searched for solutions to questions without success only to stumble on a solution weeks later. And then the solution is in an old thread that no longer allows responses. Would love to see an indexed/tagged forum where users can post articles and stays open for discussion and questions.

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