How to Filter Data Based on Comma-separated Classes

[hcho]

Hello Alteryx Community,**

I'm working on creating a macro that filters out data when they don't match one of the proposed classes.

Input

1. Data Table: This table contains the data that needs to be filtered. Here’s a snippet of the data table:

   Id      Colour   Weight

   apple1  red      5

   apple2  yellow   4

   apple3  green    6

   apple4  blue     5

   apple6           4

2. Limit Table: This table specifies the target field names, their corresponding valid classes (separated by commas), and an `EmptyOK` flag indicating whether empty values are also considered valid.

   Field   Type    Classes             EmptyOK

   Colour  Class   Red,Yellow,Green    True

Output

1. Invalid Class Table: This is the expected output, showing rows filtered out based on the example inputs.

   Id      Colour   Weight

   apple4  blue     5

I've started working on the workflow, and the files are attached as the two inputs mentioned above. I would appreciate any suggestions or guidance on how to refine this macro.

Thank you!

[griffinwelsh]

@hcho take a look at the attached standard macro. Let me know if you have any questions. Please mark the post solved if this fits your use case.

[Raj]

@hcho 
some basic question over here does your limited table have same values every time?
if yes then you almost have created the macro
just convert your input to macro input and add macro-output to "L" anchor


Hope this helps.

[hcho]

I realize I may not have explained my question clearly. The output from the J anchor in the Join tool provides the valid data, but what I need is the invalid data. So, I'm specifically interested in the output from the L anchor, which should filter out empty values. However, I've noticed that since the left table entries failed to join, they don’t have the EmptyOK information included. Is there a way to retain the EmptyOK value even after a join?

[griffinwelsh · Accepted solution]

@hcho Tried to upload this earlier but it did not work. Take a look at this macro.

[hcho]

That's perfect. Thank you, @griffinwelsh!