Filter rows with specific value contained in more than one column

[jarviscampbell]

Hello all,

Here’s an example of my data set:

Row	Col0	Col1	Col2	Col3	Col4
—	—	—	—	—	—
Row0	Blue	Yellow	Red	Green	Gray
Row1	Brown	Green	Cyan	White	Black
Row2	Pink	Blue	Yellow	Gray	Green
Row3	Red	Black	White	Gray	Brown
Row4	Cyan	Pink	Purple	Blue	Orange
Row5	Orange	Purple	Red	Black	Yellow

Searching for ‘Yellow’

I expect the result to return to me Row0, Row2 and Row5 as:

Row0
Row2
Row5

As a simple filter
I have tried How to filter-in rows that contains a specific word
however, it didn’t work for me.

I have tried other ‘Row Filters’ like the rule-based row filter and also added a loop to go over the columns as I will have hundreds of columns and thousands of rows.

I simply want to filter the row where ‘Yellow’ appears, Anywhere, it may be.

Workflow attached

Filter rows with specific value contained in more than one column.knwf (27.7 KB)

Using the “Rule-based Row filter” I have tried the ‘? IN ?’ Function but it seems to be searching for a column reference, which I cannot remotely fathom what may be if not the column itself.

I have also tried the other way around ‘just in case’ - “Yellow” IN $column1$ no luck.

Thanks a lot in advance.
J.

[gonhaddock]

Hello @jarviscampbell

For this case, you can concatenate $Col0$ to $Col4$ with the ‘Column Aggregator’ resulting a $Concatenate$ column.

Next is a ‘String Manipulation’ node with the next code, returning a Logical column TRUE/FALSE

regexMatcher($Concatenate$, 
 	join( 
 		"(.*?(" ,
 		"Yellow" ,
 		")[^$]*)$"  
 	) 
)

And latest a row filter node of TRUES

PS.- You can concatenate rules in Rule Engine with the OR logical operator. But can be complex if you have too many columns.

BR

[elsamuel]

In your 2nd workflow, the Column List Loop Start node records the name of the current column as a variable. You can configure the Row Filter node to use this variable:

At the end of the loop you’d have this:

At this point you can use a RowID node to extract the RowIDs:
:

Finally, use a column filter to keep only this RowID column.

Alternately, you can concatenate the columns like @gonhaddock said, but then use a Rule-Based Row filter with the expression $List$ LIKE "*Yellow*" => TRUE to keep all the rows with yellow. Then extract the RowIDs as above.

[ArjenEX]

As shown by the others, who beat me to it, you can approach this in many ways Another alternative is via the Create Collection route with a subsequent Row Filter checking for *Yellow*

Filtered result

[gonhaddock]

Hi again,
As suggested by @ArjenEX , you can use his Row filter configuration directly in the $Concatenate$; avoiding the ‘regexMatcher’ step.

[jarviscampbell]

Thank you everyone for such prompt results and different alternatives, I appreciate it.

@gonhaddock yours worked beautifully, now, what if I would like to take in a variable from somewhere where I could simply add ‘Yellow’ somewhere else and it would import it into this piece of the code without me having to edit the code every time.

In my real scenario I have a table w hundreds of variables. So I would like to select one of those values and it exports it to that code you beautifully created?

Thank you so much.
J.

[gonhaddock]

Yes @jarviscampbell ,
You can replace it with both, a variable or a constant column, as you prefer.

BR

[jarviscampbell]

Yes, I was wondering, how does a variable would go in it? I don’t know how to enter variables yet, I am afraid.

Meaning, I would like to select from a list. Let’s say I have a list where I can select the Yellow from it by clicking or something…

[gonhaddock]

@jarviscampbell

Your variables will be displayed in this part of the settings window marked in blue.

Let’s assume you have a variable called “Filter”. You can add it just by double clicking on it.

$${SFilter}$$

BR

[badger101]

Just to note something, I believe @ArjenEX 's method can take up variables too. But it’s up to you which solution to choose, of course.

[ArjenEX]

It does. For convenience you can also create a simple component for it whereby you only have to type in the desired value between the wildcard and the variable is passed along subsequently.

Inner:

See WF:
Filter rows with specific value contained in more than one column - component.knwf (32.1 KB)

[badger101]

Yes that’s what I imagined. You can also make it more advanced though, by having a pre-made list of the variable strings to choose from, since @jarviscampbell mentioned “In my real scenario I have a table w hundreds of variables. So I would like to select one of those values…” .

Versus rather than having to type it.

[ArjenEX]

Yes I recalled reading that as well afterwards Dynamically generated drop-down list would be the way to go.

[jarviscampbell]

Is there a node for it or something pre-made on Knime?

[badger101]

Yes @jarviscampbell . As @ArjenEX pointed out, a drop-down list can be used. You may see all available options that you think are suitable / to your liking by typing ‘widget’ on your KNIME searchbox (i.e. Node repository).

[jarviscampbell]

Hi @gonhaddock

I am still trying to add your solution without any luck, what am I missing?

Filter rows with specific value contained in more than one column-AddingVariable.knwf (20.1 KB)

[gonhaddock]

Hi,
You tried to create a a column variable, but unfortunately this doesn’t exist and your variable either. In fact you have created a variable for every single Row from your ‘colorsVariable’ Table

Looking into it, it isn’t clear the output that you are trying to achieve.

Variables are fed in a one by one step. Maybe you want to loop through them (?)

BR

[jarviscampbell]

From the drop-down list (the widget mentioned - still looking how to add) I would select a Color as a variable.

Upon selecting, that color/variable would link directly to either the regex function or the row filter. That’s what I am trying to achieve.

I also don’t know what I did on the example above. I certainly don’t want to have to have (unless I have no easier option) to assign a table variable to 100+ rows in order to achieve what I just explained. Thanks.

[jarviscampbell]

I will add here how I was able to add a ‘drop-down’ / ‘select list’ to the filter, I hope it help others

Filter rows with specific value contained in more than one column-wVariableFromDropDownList.knwf (20.4 KB)

[jarviscampbell]

used the ‘Single selection configuration’ node from ‘Workflow Abstraction’

Thank you guys
J.

Filter rows with specific value contained in more than one column-wVariableFromDropDownList.knwf (20.4 KB)