Remove rows that contains values from another table's column?

[LoganRay]

Is there a way to remove rows from a table that contain strings found in another table? The article below is very close to what I want to do, but it’s an exact match removal rather than removing all values that contain.

For example, if my main table has the following values:
nike shoes
running shoes
tennis shoes
hiking boots

And my table for things to remove has “shoes” in it, only ‘hiking boots’ would remain.

Continuing the discussion from How can we remove the rows after checking with the another table?:

[badger101]

Hi @LoganRay Depends on what patterns the main table take. Does each row contain only two words, and the second word is the one that’s used to match with the reference table? In that case, you’ll have to use the cell splitter node to split the two words first. Then use the second column to match with the referenced table, via the Reference Row Filter Node. (Assuming the reference table consist of only one word too per row).

[LoganRay]

Thanks for the response @badger101

There are a variable amount of words in the main table column. If it weren’t for that, the cell splitter method would be perfect.

[badger101]

And the reference table has only one word (per row)?

[badger101]

Topic Extractor Node

First we lowercase to get the best possible matches as upper and lowercase letters are not the same for computers. Then we use a little regex joining all the search terms together with | (which means “OR” in regex). Search for the word apple or dog ==> apple|dog Similarity Search 2.knar.knwf (52.0 KB) [Screen Shot 2022-04-05 at 12.06.03 PM] [Screen Shot 2022-04-05 at 12.05.48 PM] This solution actually works better on this sample of data, but if you have a lot of misspellings, this won’t…

Will this solution work for your case?

[victor_palacios]

I was just about to suggest that workflow as well. Just use the Similarity Search 2 workflow and if a match exists, filter out that row.

[badger101]

Yeah I remembered your post in that thread @victor_palacios Cause I included that solution in my personal repository

[LoganRay]

Not quite, I should’ve mentioned I’ll have several single-word lines in the exclude list. I can do this with a python node, but I’d prefer a purpose-built node/workflow if that’s possible.

[badger101]

@LoganRay If I’m not mistaken, Victor’s solution is built for cases like yours. Try to download his and have a look carefully.

Otherwise, if you don’t have the luxury of time for some reason, send your dataset here. I’ll have a look at what I can do.

[LoganRay]

Thanks @badger101 and @victor_palacios

I was able to use this with some slight tweaks, namely to apply a row filter after the regex and remove any rows that contain values in the list.

[badger101]

Awesome! From the title of this post, I believe it’s gonna help future answer seekers find solutions fast for cases like this when they do their forum search.

[LoganRay]

Here’s my version of the workflow. The output is the rows that do not contains one of the matchback values.

Remove Rows That Contain.knar.knwf (52.2 KB)

[Daniel_Weikert]

another option could be to adjust your regex a little furter

br