# How to make such a node from alteryx in knime?

**URL:** <https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268>\
**Category:** KNIME Analytics Platform\
**Created:** [December 27, 2023, 3:26pm UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268 "2023-12-27T15:26:01Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![divaice71](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/e5b9ba/32.png) [@divaice71](https://forum.knime.com/u/divaice71)\
**Post date:** [December 27, 2023, 3:26pm UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268/1 "2023-12-27T15:26:01Z")

</div>

![dd](https://forum-cdn.knime.com/uploads/default/original/3X/f/f/ffa9ee3f86bd9100a9b5b1a9fbc2fffb590baad0.jpeg)

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.

---

<div class="post-metadata">

**Author:** ![Daniel\_Weikert](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/e47c2d/32.png) [@Daniel\_Weikert](https://forum.knime.com/u/Daniel_Weikert)\
**Post date:** [December 27, 2023, 4:38pm UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268/2 "2023-12-27T16:38:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rfeigel](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/r/7ea924/32.png) [@rfeigel](https://forum.knime.com/u/rfeigel)\
**Post date:** [December 27, 2023, 8:23pm UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268/3 "2023-12-27T20:23:54Z")

</div>

What does “using conditions” mean?

---

<div class="post-metadata">

**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\
**Post date:** [December 27, 2023, 8:30pm UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268/4 "2023-12-27T20:30:42Z")

</div>

@divaice71 you can also take a look at this book

> **[From Alteryx to KNIME | KNIME](https://www.knime.com/knimepress/from-alteryx-to-knime)**
>
> See KNIME vs Alteryx in action. This guide goes through the steps you'd take in Alteryx, and how you'd do the same thing in KNIME Analytics Platform.

---

<div class="post-metadata">

**Author:** ![divaice71](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/e5b9ba/32.png) [@divaice71](https://forum.knime.com/u/divaice71)\
**Post date:** [December 27, 2023, 8:57pm UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268/5 "2023-12-27T20:57:38Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![divaice71](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/e5b9ba/32.png) [@divaice71](https://forum.knime.com/u/divaice71)\
**Post date:** [December 27, 2023, 8:58pm UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268/6 "2023-12-27T20:58:48Z")

</div>

If else conditions as on the screen

---

<div class="post-metadata">

**Author:** ![divaice71](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/e5b9ba/32.png) [@divaice71](https://forum.knime.com/u/divaice71)\
**Post date:** [December 27, 2023, 9:01pm UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268/7 "2023-12-27T21:01:08Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![rfeigel](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/r/7ea924/32.png) [@rfeigel](https://forum.knime.com/u/rfeigel)\
**Post date:** [December 27, 2023, 9:01pm UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268/8 "2023-12-27T21:01:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\
**Post date:** [December 27, 2023, 10:41pm UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268/9 "2023-12-27T22:41:51Z")

</div>

@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](https://forum.knime.com/t/sumif-and-conditional-formatting-ideas/30962/2):
>
> 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?](https://forum.knime.com/t/how-to-do-a-sum-with-condition/12230/3):
>
> you cloud use a Rule Engine node to define some rule before aggregating the values, so you could define more complex scenarios. [GroupBy Year and Month.knwf](https://forum-cdn.knime.com/uploads/default/original/2X/b/bb99490b0d872fb782cb8cf58a8ce42a3a708b08.knwf) (17.1 KB)

> [@Count with condition](https://forum.knime.com/t/count-with-condition/63825/2):
>
> Hi @Paolodam Something like this should get you moving forward: Some steps: Evaluate each site as a group by initiating a Group Loop based on the respective column. 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 …

---

<div class="post-metadata">

**Author:** ![hmfa](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/h/9fc29f/32.png) [@hmfa](https://forum.knime.com/u/hmfa)\
**Post date:** [December 28, 2023, 7:06am UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268/10 "2023-12-28T07:06:18Z")

</div>

Hi @divaice71

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

> [@Multiple Rolling Excel Sumifs](https://forum.knime.com/t/multiple-rolling-excel-sumifs/31496/2):
>
> 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](https://forum.knime.com/t/sumifs-excel/13710/14)@elsamuel also provided an example for this: [Sumif and Conditional formatting ideas - #2 by elsamuel](https://forum.knime.com/t/sumif-and-conditional-formatting-ideas/30962/2)

Regards

---

<div class="post-metadata">

**Author:** ![J\_Knime\_Work](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/j/0ea827/32.png) [@J\_Knime\_Work](https://forum.knime.com/u/J_Knime_Work)\
**Post date:** [January 3, 2024, 12:45pm UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268/11 "2024-01-03T12:45:00Z")

</div>

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](https://hub.knime.com/knime/extensions/org.knime.features.expressions/latest/org.knime.expressions.base.node.formulas.FormulasNodeFactory)

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

---

<div class="post-metadata">

**Author:** ![takbb](https://forum-cdn.knime.com/user_avatar/forum.knime.com/takbb/32/57077_2.png) [@takbb](https://forum.knime.com/u/takbb)\
**Post date:** [January 3, 2024, 4:31pm UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268/12 "2024-01-03T16:31:33Z")

</div>

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:

```auto
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:

```auto
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”](https://forum.knime.com/t/column-expressions-have-long-memories-too/76048):

---

<div class="post-metadata">

**Author:** ![divaice71](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/e5b9ba/32.png) [@divaice71](https://forum.knime.com/u/divaice71)\
**Post date:** [January 3, 2024, 8:28pm UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268/13 "2024-01-03T20:28:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![divaice71](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/e5b9ba/32.png) [@divaice71](https://forum.knime.com/u/divaice71)\
**Post date:** [January 3, 2024, 8:31pm UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268/14 "2024-01-03T20:31:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Bytecrawler](https://forum-cdn.knime.com/user_avatar/forum.knime.com/bytecrawler/32/37359_2.png) [@Bytecrawler](https://forum.knime.com/u/Bytecrawler)\
**Post date:** [January 5, 2024, 5:23am UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268/15 "2024-01-05T05:23:55Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![takbb](https://forum-cdn.knime.com/user_avatar/forum.knime.com/takbb/32/57077_2.png) [@takbb](https://forum.knime.com/u/takbb)\
**Post date:** [January 5, 2024, 7:16am UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268/16 "2024-01-05T07:16:39Z")

</div>

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](https://forum.knime.com/t/java-snippets-have-long-memories/33125)” and “[Column Expressions have long memories too!](https://forum.knime.com/t/column-expressions-have-long-memories-too/76048)” post I referenced above. I hope you find them useful 😀.

---

<div class="post-metadata">

**Author:** ![Bytecrawler](https://forum-cdn.knime.com/user_avatar/forum.knime.com/bytecrawler/32/37359_2.png) [@Bytecrawler](https://forum.knime.com/u/Bytecrawler)\
**Post date:** [January 5, 2024, 5:00pm UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268/17 "2024-01-05T17:00:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\
**Post date:** [January 12, 2024, 5:00pm UTC](https://forum.knime.com/t/how-to-make-such-a-node-from-alteryx-in-knime/76268/18 "2024-01-12T17:00:40Z")

</div>

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