Filter out rows based on a condition that applies to ALL columns without hardcoding the column names

[nicole1201]

Hello I am still very new to KNIME and I have a problem.

So I have this table as input,

I want to have a rule where if there’s 0 in any of the columns for each row, I will include the row. If there’s no 0 at all like in row 6 then remove it. So the resultant table should only have rows 1,3,4,5.

How do I acheive this outcome without hardcoding (meaning without referencing any of the column names)? Thanks for all the help!

[gonhaddock]

Hello @nicole1201

My suggest is to unpivot your data; once in a single column it is easy to match == 0 TRUE / FALSE with String Manipulation if ‘string’ as in your example, or Math formula if ‘numeric’. Then you will have to ‘Pivot’ back the LOGICS and join to the original table.

The following step is with a Column Aggregator for your LOGICS by using Concatenate as Aggregation Setting. And finally a new ‘String Manipulation’ node to match the TRUE content in the concatenated column of LOGICS. The resulting TRUE / FALSE column will be your Filter Column.

Let me know if further advice is needed.

BR

[ArjenEX]

Hi @nicole1201

Another way would be to directly use the Column Aggregator with a wildcard column selection to concatenate all columns.

Followed by a Nominal Value Row Filter to filter out the rows with a 0 by using a (.*\D[0].*)|([0].*) Regex (which translates to contains a zero or starts with a zero) which filters rows 1,3,4 and 5 like you mention.

[nicole1201]

@ArjenEX

(.\D[0].)|([0].*)

This is a great solution! However, my values are not integers. Meaning to say that some of my values are 10.04 and rows that have no 0 but contains these decimal places seem to get included as well. Do you perhaps have any workaround for this?
Thanks a ton!

[bruno29a]

Hi @nicole1201 , I’ve done something for a similar request in the forum, I just can’t find it.

Something like this will work for you:

The idea is to:

1. Add all the columns of each row into a collection
2. Include only the rows whose generated collection has “0” in it

In details:
Creating the collection (set):

Since I am using “Enforce exclusion” and not excluding anything, it will automatically include all the columns (so no need to hardcode any column names).

I’m also selecting the option of not storing duplicate values since it only take 1 “0” from any column for the row to be included (the search will be faster).

This is what we get as generated collection (set):

Then we just need to filter on the collection that has “0” in it:

"0" IN $AggregatedValues$ => TRUE

And we get Row 1, 3, 4, 5 as expected:

You can of course remove the AggregatedValues column with a Column filter after.

Here’s the workflow: Filter out rows based on condition and without hardcoding col names.knwf (9.1 KB)

[ArjenEX]

Hi @nicole1201

Well, both mine and @bruno29a solution is working based on string values, like you have used in your opening post

If you change
"0" IN $AggregatedValues$ => TRUE
to
"0.0" IN $AggregatedValues$ => TRUE
it can capture doubles.