Filter based on a reference table

Hi,
I have a data table with a column of errors for each individual, which can have more than one error message per person concatenated, and there is a master table of errors, how is it possible to filter rows in the data table , leaving only the rows that have inconsistency messages contained in the master table?

ID ERROR
1 INSUFFICIENT CAPITAL
2 INSUFFICIENT CAPITAL, INSUFFICIENT DATA
3 INSUFFICIENT DATA, INCONSISTENT ID
4 INSUFFICIENT DATA, INSUFFICIENT CAPITAL, INCONSISTENT ID

Master table:
ERROR
INSUFFICIENT DATA
INCONSISTENT ID

In this case they should only follow the id 2, 3 and 4 with a reference filter based on the error master.

It does not help me to make a cell splitter, because I can have n concatenated inconsistency messages, I was trying with a dictionary filter but it did not work for me.
Does anyone know how I can achieve what I need?

You need Rule-based Row Filter (Dictionary) — NodePit
node.
Here’s an example
rule_based_filter_dictionary [Workflow] — NodePit

Hello @daviddelos,

just to check if I got it right. You need to keep table row if one of errors from master table is found in error column?

Br,
Ivan

Hi @ipazin , Yeah that’s right

Hi @izaychik63 ,
Thanks for answering.
I have been testing with that node, however it doesn’t work for me, it also takes a long time to process as there is a lot of data.

I was able to solve it by doing a Cell Splitter to the inconsistencies column, then the columns resulting from the divisor I converted into a collection with Create Collection Column, and then that column resulting from the collection I applied an Ungroup, this way I already received a message of inconsistency per row and I was able to do a Reference Row Filter with the inconsistency master.

1 Like

@daviddelos , it’s great you find solution working for you. In case of Rule-based row filter you can use $ID ERROR$ LIKE “*INSUFFICIENT DATA*” => TRUE rule to compare rows content to the Maser Table value.

Hello @daviddelos,

standard “trick” in this case is to build regular expression from keywords and use them in Row Filter node. Check example on Hub:

Br,
Ivan

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.