How to Filter Data Based on Multiple Limit Conditions in a Table Using Alteryx Tools

[hcho]

Hello Alteryx Community,

I want to filter out-of-range data based on multiple limit conditions provided in a separate table. I have been using Python for this task, but it is taking too long to process, so I am looking for a way to solve this using Alteryx tools. I will attach two input files and the workflow I made with Python for reference.

Input

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

Industry        Name                  Age  YearsOfEducation  YearsAtCurrentJob  Salary
Communication   Ronald Dryer          43   16                5                 83000
Manufacturing   Angelina Prissant     33   18                4                 87000
Manufacturing   Tara Jackson          28   12                6                 35000
Communication   Keith Jacobs          56   14                8                 63000
Manufacturing   Terrence McKinley     26   18                2                 80000
Manufacturing   Wilma Holmes          63   16                12                77000
Communication   Mark Hodges           46   12                2                 34500
Software        Danielle Rodriguez    29   22                1                 108000
Software        Michael Lawrence      53   16                7                 93000
Software        Christina Kim         32   16                4                 88000

2. Limit Table - This table contains the target field names and their corresponding min and max values for each field. Here is a snippet of the limit table:

Field            Min  Max
Age              20   30
YearsOfEducation 10   15

Output

1. Filtered Table: The filtered out-of-range data should include an additional field named Out-Of-Range Field, indicating which field caused the row to be filtered out. Here is an example of the expected output:

Industry        Name                  Age  YearsOfEducation  YearsAtCurrentJob  Salary  Out-Of-Range Field
Software        Christina Kim         32   16                4                 88000   Age
Communication   Ronald Dryer          43   16                5                 83000   YearsOfEducation
Manufacturing   Angelina Prissant     33   18                4                 87000   YearsOfEducation
Manufacturing   Terrence McKinley     26   18                2                 80000   YearsOfEducation
Manufacturing   Wilma Holmes          63   16                12                77000   YearsOfEducation
Software        Danielle Rodriguez    29   22                1                 108000  YearsOfEducation
Software        Michael Lawrence      53   16                7                 93000   YearsOfEducation
Software        Christina Kim         32   16                4                 88000   YearsOfEducation

Requirement

I need a workflow that returns the rows in the data table outside the specified limits for each field as defined in the limit table. The method should be expandable, allowing multiple limit conditions in the table.

Desired Workflow

1. Read the Data Table.
2. Read the Limit Table.
3. Apply the filtering based on the conditions specified in the Limit Table.
4. Output the filtered Data Table.

I would appreciate any guidance on which Alteryx tools to use and how to configure them to efficiently achieve this filtering process. Thank you for your help!

[cjaneczko]

How many limits are you dealing with? An ElseIf formula tool can handle this. 

[griffinwelsh · Accepted solution]

@hcho You may want to modify this to fit your needs, but this general structure should work well.

1.Assign record ID

2. Transpose each field with record id as key

3. Join on name

4. Apply filter with between function

5. Get unique recorids that were filtered out

6. Join with original data set on record id

[hcho]

The number of limits is variable. I want to make this solution adaptable so that it can be applied to any other tables with different limits. Therefore, a flexible approach that can handle an arbitrary number of limits is needed. Could you provide guidance on achieving this in Alteryx?

[griffinwelsh]

@hcho My solution should work for any number of conditions assuming they are all min max conditions. You could also modify it slightly to handle string match conditions as well. Thanks for posting this question. This is a really elegant way to maintain this type of filtration system and I'm sure I will find applications for this method in my own work.

[hcho]

@griffinwelsh I tested your solution, and it works beautifully. Thank you for sharing such an elegant solution!