# Question about multi-row formula in Alteryx **URL:** \ **Category:** KNIME Analytics Platform\ **Created:** [November 7, 2024, 3:15am UTC](https://forum.knime.com/t/question-about-multi-row-formula-in-alteryx/84299 "2024-11-07T03:15:14Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![ThomasChen](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/t/eb9ed0/32.png) [@ThomasChen](https://forum.knime.com/u/ThomasChen)\ **Post date:** [November 7, 2024, 3:15am UTC](https://forum.knime.com/t/question-about-multi-row-formula-in-alteryx/84299/1 "2024-11-07T03:15:14Z")
Hi guys, I want to migrate Alteryx workflow into KNIME workflow. There is a multi-row formula node in Alteryx like this. ![image](https://forum-cdn.knime.com/uploads/default/original/3X/a/9/a974cf75e13a1ef13ad9a0b23f3041e809125fd0.png) It simply gives values to column(Group ID) according to the values in column (2). If column(2) contains “com”, the values in column(Group ID) +1, else the values in column(Group ID) = value in the last row. The output should look like this. ![image](https://forum-cdn.knime.com/uploads/default/original/3X/c/9/c92d7dabb24e329337f13fa76bd632306b42b9f5.png) Can anyone help me solve this problem? Thanks! ---
**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:** [November 7, 2024, 4:37am UTC](https://forum.knime.com/t/question-about-multi-row-formula-in-alteryx/84299/2 "2024-11-07T04:37:01Z")
Your more likely to get help if you share some sample data. The Column Expressions node may work, but its hard to tell without some data. ---
**Author:** ![ThomasChen](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/t/eb9ed0/32.png) [@ThomasChen](https://forum.knime.com/u/ThomasChen)\ **Post date:** [November 7, 2024, 6:11am UTC](https://forum.knime.com/t/question-about-multi-row-formula-in-alteryx/84299/3 "2024-11-07T06:11:55Z")
The sample data is just like column(2) in the above picture. ---
**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:** [November 7, 2024, 6:57am UTC](https://forum.knime.com/t/question-about-multi-row-formula-in-alteryx/84299/4 "2024-11-07T06:57:40Z")
Hi @ThomasChen , what I think @rfeigel meant was that if you can upload the sample data as text it makes it much quicker to help. Otherwise he or somebody else has to type in the data which is laborious and time consuming. You can upload a file (eg csv) or you can write it as a table directly in the post. If you don’t know how to write it in a post as a table, I have a component which can help with that… 😉 > [@Easy creation of formatted "table" on forum posts](https://forum.knime.com/t/easy-creation-of-formatted-table-on-forum-posts/79212): > > If you ever want to paste a copy of your table data into the forum so that it is easy to read, I have a new component just for that purpose: Simply direct the output from your KNIME table to it, and execute the Clipboard Writer, and it writes a copy of the table to clipboard in a format that can be pasted directly into a forum post: You can configure it to limit output to the first n rows, and also filter the columns. For example, executing the workflow pictured results in me bein… ---
**Author:** ![MartinDDDD](https://forum-cdn.knime.com/user_avatar/forum.knime.com/martindddd/32/69988_2.png) [@MartinDDDD](https://forum.knime.com/u/MartinDDDD)\ **Post date:** [November 7, 2024, 7:37am UTC](https://forum.knime.com/t/question-about-multi-row-formula-in-alteryx/84299/5 "2024-11-07T07:37:15Z")
Hey there, agree with @takbb and @rfeigel - makes life way easier… That said I am in an exceptionally good mood this morning so here is one approach. I don’t think there’s yet an as elegant way as in Alteryx as when using Multirow access e.g. in column expressions you cannot access the values of previous rows of the column you are currently creating. There are some “hacky” ways I think that involve some Java Script that @takbb I think worked out - however if you want to go the low code way this can work: 1. Filter for contains _.com_ 2. To the filtered table, add a running number ( I use rowIndex() in column expressions 3. Join the filtered data set back with the main data set incl. left unmatched 4. now use Missing values first to fill missing values in the Group ID column with the “previous value” - now you have all Groups 1 and over sorted 5. Add another missing value node and set anything else to 0… Overview: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/4/e/4e6a56f735c2d260d6fbd3cc8fc26568d7d6c604.png) WF: [alteryixmultirow.knwf](https://forum.knime.com/uploads/short-url/iOh1IoFqMUuDWxVVxPUQXnXfj8L.knwf) (82.4 KB) ---
**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:** [November 7, 2024, 7:59am UTC](https://forum.knime.com/t/question-about-multi-row-formula-in-alteryx/84299/6 "2024-11-07T07:59:16Z")
> [@MartinDDDD](#): > > There are some “hacky” ways I think that involve some Java Script that @takbb I think worked out Ah yes, thanks for the reminder @MartinDDDD🙂… the java snippet can be coded in such a way that it can remember the previous value but… > [@Column Expressions have Long Memories too!](https://forum.knime.com/t/column-expressions-have-long-memories-too/76048/1): > > 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. It’s actually relatively straightforward with Column Expressions, once you know the principle. (see above post) Granted, it’s still a bit “hacky”, lol. ---
**Author:** ![ThomasChen](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/t/eb9ed0/32.png) [@ThomasChen](https://forum.knime.com/u/ThomasChen)\ **Post date:** [November 7, 2024, 8:35am UTC](https://forum.knime.com/t/question-about-multi-row-formula-in-alteryx/84299/7 "2024-11-07T08:35:11Z")
Thanks for your reply, but there is something wrong with your answer. The problem is that when there are two identical values in the column(2), the join result will be wrong. For example, for these values in column(2) 0 1com 1 2 3 4 1com 123djs adfdc sdc sdcsvcom sadd asffvf the join result will be | ? | 0 | | --- | --- | | 1 | 1com | | 2 | 1com | | ? | 1 | | ? | 2 | | ? | 3 | | ? | 4 | | 1 | 1com | | 2 | 1com | | ? | 123djs | | ? | adfdc | | ? | sdc | | 3 | sdcsvcom | | ? | sadd | | ? | asffvf | | Do you know how to solve this problem? | | ---
**Author:** ![MartinDDDD](https://forum-cdn.knime.com/user_avatar/forum.knime.com/martindddd/32/69988_2.png) [@MartinDDDD](https://forum.knime.com/u/MartinDDDD)\ **Post date:** [November 7, 2024, 8:50am UTC](https://forum.knime.com/t/question-about-multi-row-formula-in-alteryx/84299/8 "2024-11-07T08:50:28Z")
This should not happen if you join the tables on RowID as they are the same in both tables. You don’t join on the content of column(2), but on the RowID (as per my example). … and this is where it becomes incredibly helpful if you can actually share your workflow as well… ---
**Author:** ![ThomasChen](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/t/eb9ed0/32.png) [@ThomasChen](https://forum.knime.com/u/ThomasChen)\ **Post date:** [November 7, 2024, 9:28am UTC](https://forum.knime.com/t/question-about-multi-row-formula-in-alteryx/84299/9 "2024-11-07T09:28:00Z")
I see. Next time I will share my workflow. Your solution works. Thank you very much. ---
**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:** [November 7, 2024, 9:40am UTC](https://forum.knime.com/t/question-about-multi-row-formula-in-alteryx/84299/10 "2024-11-07T09:40:55Z")
btw, and just for info, as this is already solved… The “hacky” 😉 Column Expression would be this: ```auto var prevGroupID if(prevGroupID==null)prevGroupID=0 /* initialises prevGroupID only once ( first row only ) */ //----------------------------------------------------------------------------- if(indexOf(column("2"),".com")== -1 ) { prevGroupID } else { ++prevGroupID /* add 1 to previous group if we have found .com */ } ``` ![image](https://forum-cdn.knime.com/uploads/default/original/3X/6/5/6519502fbcca9e5330829522631a5e06c90c821b.png) ---
**Author:** ![nan](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/n/cc9497/32.png) [@nan](https://forum.knime.com/u/nan)\ **Post date:** [November 7, 2024, 10:17am UTC](https://forum.knime.com/t/question-about-multi-row-formula-in-alteryx/84299/11 "2024-11-07T10:17:33Z")
Wow, you are in a really hacking mood today. There is a much simpler solution to this problem (if I understand it correctly). ![image](https://forum-cdn.knime.com/uploads/default/original/3X/7/5/7538fdaa22cb029d7a7858ad9d1b9459f00fc00c.png) The rules in the Rule Engine are as simple as ```auto $2$ LIKE "*.com*" => 1 TRUE => 0 ``` [count com.knwf](https://forum.knime.com/uploads/short-url/8C5cUrOyfclgctjSqb0BW19k4AN.knwf) (75.3 KB) Have a nice day, nan ---
**Author:** ![MartinDDDD](https://forum-cdn.knime.com/user_avatar/forum.knime.com/martindddd/32/69988_2.png) [@MartinDDDD](https://forum.knime.com/u/MartinDDDD)\ **Post date:** [November 7, 2024, 10:18am UTC](https://forum.knime.com/t/question-about-multi-row-formula-in-alteryx/84299/12 "2024-11-07T10:18:50Z")
just love it how “there are so many ways leading to rome” in KNIME 🙂 ---
**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\ **Post date:** [November 14, 2024, 10:19am UTC](https://forum.knime.com/t/question-about-multi-row-formula-in-alteryx/84299/13 "2024-11-14T10:19:19Z")
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.