Question about multi-row formula in Alteryx

[ThomasChen]

Hi guys, I want to migrate Alteryx workflow into KNIME workflow. There is a multi-row formula node in Alteryx like this.

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.

Can anyone help me solve this problem? Thanks!

[rfeigel]

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.

[ThomasChen]

The sample data is just like column(2) in the above picture.

[takbb]

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

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: [image] 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…

[MartinDDDD]

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:

WF:
alteryixmultirow.knwf (82.4 KB)

[takbb]

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

@takbb

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.

[ThomasChen]

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?

[MartinDDDD]

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…

[ThomasChen]

I see. Next time I will share my workflow. Your solution works. Thank you very much.

[takbb]

btw, and just for info, as this is already solved…

The “hacky” Column Expression would be this:

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 */
}

[nan]

Wow, you are in a really hacking mood today. There is a much simpler solution to this problem (if I understand it correctly).

The rules in the Rule Engine are as simple as

$2$ LIKE "*.com*" => 1
TRUE => 0

count com.knwf (75.3 KB)

Have a nice day,
nan

[MartinDDDD]

just love it how “there are so many ways leading to rome” in KNIME