How to make such a node from alteryx in knime?

[divaice71]

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.

[Daniel_Weikert]

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

[rfeigel]

What does “using conditions” mean?

[mlauber71]

@divaice71 you can also take a look at this book

From Alteryx to KNIME | KNIME

[divaice71]

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

[divaice71]

If else conditions as on the screen

[divaice71]

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.

[rfeigel]

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

[mlauber71]

@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.

Sumif and Conditional formatting ideas

Hi @sammeredith, welcome to the forum. Here’s a workflow that should get you started. Conditional formatting isn’t really a thing in KNIME unless you’re going to be exporting to Excel. In that case, you’d use the XLS Coloured Writer node and specify your conditions.

How to do a SUM with condition?

you cloud use a Rule Engine node to define some rule before aggregating the values, so you could define more complex scenarios. [image] GroupBy Year and Month.knwf (17.1 KB)

Count with condition

Hi @Paolodam Something like this should get you moving forward: [image] Some steps: Evaluate each site as a group by initiating a Group Loop based on the respective column. [image] Next task is the determine the start point of each measurement which has to be done dynamically off course. One way to determine is evaluating each row if either parameter 1 or 2 contains a record. If such a record is found, output the RowIndex -1 for later filtering usage. I opt for a Column Expression using …

[hmfa]

Hi @divaice71

You can search the forum for “sumif”. There are some responses.
Ex: have a look at

Multiple Rolling Excel Sumifs

Question on SUMIF came up a couple of times in the forum. @ipazin created an example for this and shared it in this thread: Sumifs Excel - #14 by ipazin @elsamuel also provided an example for this: Sumif and Conditional formatting ideas - #2 by elsamuel

Regards

[J_Knime_Work]

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.

[takbb]

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”:

[divaice71]

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

[divaice71]

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

[Bytecrawler]

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.

[takbb]

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 .

[Bytecrawler]

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.