Filter Tool Icon

Filter Tool

Version:
Current
Last modified: January 30, 2020

Use Filter to select data using a condition. Rows of data that meet the condition are output to the True anchor. Rows of data that do not meet the condition are output to the False anchor. 

The Filter tool can...

  • Select rows by comparing a column against a static value. See an example.
  • Select rows with or without missing data. See an example.
  • Select rows using date-time data. See an example.
  • Select rows using a multiple-column condition. See an example.
  • Select rows using a compound condition. See an example.

Tool Components

Screenshot of the filter tool on the Designer canvas

The Filter tool has 3* anchors. 

  • Input anchor: Use the input anchor to select the data you want to filter.
  • T (True) anchor: The True anchor outputs the rows of data that meet the filter condition.
  • F (False) anchor: The False anchor outputs the rows of data that do not meet the filter condition.

*Because the Filter tool includes an expression editor, an additional input anchor displays when you use Filter in an app or macro. Use the Interface tools to connect to a Question anchor. See Interface Tools.

Tool Configuration

Select Basic filter or Custom filter

  • Use the Basic filter to build a simple query on a single column of data. 
  • Use the Custom filter to build complex conditions or conditions using more than one column. 

Screenshot of Filter configuration window with options to select Basic filter or Custom filter.

Build a Basic Filter

  1. Select the column of data to filter by in the Select column dropdown. 
  2. Select the operator in the next dropdown. Available operators depend on the data type of the column you selected to filter by. See Basic Filter Operators below.
  3. Enter a value to complete the condition.
    Screenshot of a Basic filter setup

Basic Filter Operators

Available operators depend on the data type of the column you select to filter by. Learn more about Data Types.

Operator Description Available Data Types
= Value matches input Numeric, Date, DateTime
Equals Value matches input String
!= Value does not match input Numeric, Date, DateTime
Does not equal  Value does not match input String
> Value is greater than input Numeric, Date, DateTime
>= Value is greater than or equal to input Numeric, Date, DateTime
< Value is less than input Numeric, Date, DateTime
<= Value is less than or equal to input Numeric, Date, DateTime
Is null Value is a missing or unknown value Numeric, Date, DateTime, Time, String, Spatial, Boolean
Is not null Value is not a missing or unknown value Numeric, Date, DateTime, Time, String, Spatial, Boolean
Comes before (<) Value sorts to come before input String
Comes after (>)  Value sorts to come after input String
Contains  Value is found in any part of the string String
Does not contain  Value is not found in any part of the string (case sensitive) String
Is empty Value is Null or "" String
Is not empty  Value is not Null or "" String
Range Value is a selected start and end date Date, DateTime
Start date and periods after Value is the selected first date and a set number of periods that follow Date, DateTime
End date and periods before Value is the select last date and a set number of periods that precede Date, DateTime
Is true Value is true Boolean
Is false Value is not true Boolean

Build a Custom Filter Using Expressions

To build a custom filter condition, enter your expression syntax into the expression editor. There are also many tools to assist with building Custom filter conditions. See Expression Editor Tips

Screenshot of a Custom filter setup

Expression Editor Tips

These tools are available within the expression editor to assist with building Custom filter conditions.

  1. Press Ctrl + Space to view a list of all functions.
  2. Type a word or phrase to view a matching list of functions.
  3. Type a [ (left bracket) to view a list of variables to use in the expressions. Variables can be...
    • Data from an incoming connection or a column created in a previous expression.
    • Values from an Interface tool connected to the Question anchor of a tool with an expression editor when the tool is used in an app or macro. Use the Question anchor when you want to use question values as variables in the expression. See Interface Tools.
    • Global variables that are defined in workflow configuration. See Constants
  4. Select the fx icon to search and browse functions. See Functions.
  5. Select the x icon to browse or search columns and constants.
  6. Select the folder icon to browse or search recent and saved expressions.

Save a Custom Filter

Once you have built an expression you can save it for later use. Select the save icon. Enter your expression name and select Save.

Tool Examples

Example 1. Select Rows Using a Basic Comparison

Use the Basic filter to select rows by comparing a column against a static value.

Screenshot of a Basic filter with the condition CustomerID is greater than 30

According to the condition of the above Basic filter, rows with a CustomerID value greater than 30 are output to the True anchor. 

Screenshot of the True anchor results which includes all rows where CustomerID is greater than 30

Rows with a CustomerID value that is not greater than 30 are output to the False anchor.

Screenshot of the False anchor results which includes all rows where CustomerID is not greater than 30

Example 2. Select Rows With our Without Missing Data

Use the Basic filter to select rows without missing data. 

Use the Basic filter to select records without missing data.

According to the condition of the above Basic filter, rows with a LastName value that is not null (missing) are output to the True anchor.

Rows with a LastName that is not null (missing) are output to the True anchor.

Rows with a null value in LastName are output to the False anchor.

Screenshot of the False anchor results which shows all rows with a null value in LastName

 Example 3. Select Rows Using Date-Time Data

Use the Basic filter to select rows with a relative date.  

Screenshot of a Basic filter with the condition JoinDate is less than or equal to todaye.  

According to the condition of the above Basic filter, rows with a JoinDate that is less than or equal to today's date are output to the True anchor.

Screenshot of True anchor results which shows rows with a JoinDate that is less than or equal to today's date

Rows with a date after today's date are output to the False anchor.

Screenshot of False anchor results which shows rows with a JoinDate after today's date

Date-Time Filter Tips

  • The dynamic options, today, tomorrow, and yesterday, update the workflow to that relative date when the workflow is run.
  • The Start date and periods after or End date and periods before operators allow you to specify a date range by selecting a specific date, a Period type (Days, Months, Weeks, Quarters, or Years), and the Number of periods before or after.
  • Select Filter only Date data to truncate date-time data to use only the date data.

 Example 4. Select Rows Using a Multiple-Column Condition

Use the Custom filter to create a condition that references more than one column to select rows. 

Screenshot of a custom filter with the condition JoinDate is greater than or equal to the FirstPurchaseDate 

According to the condition of the above Custom filter, rows with a JoinDate that is greater than or equal to the FirstPurchaseDate are output to the True anchor. 

Screenshot of the True anchor results which shows rows with a JoinDate that is greater than or equal to the FirstPurchaseDate

Rows with a JoinDate that is less than the FirstPurchaseDate are output to the False anchor. In this case there are no rows that do not have a JoinDate less than the FirstPurchaseDate.

Example 5. Select Rows Using a Compound Condition

Use the Custom filter to create compound conditions, that is, more than one condition joined by and/or operators. 

Screenshot of a Custom filter with the condition where the region is either South or contains the word West 

According to the condition of the above Custom filter, rows where the region is either South or contains the word West are output to the True anchor. 

 Screenshot of the True anchor results which shows rows where the region is either South or contains the word West 

All other rows are output to the False anchor. 

Screenshot of the False anchor results which shows all rows where region is not South and does not contain the word West

Troubleshooting

Treat numbers with more than 15 digits as strings to prevent loss of precision. You can set the field type to a string using the Select Tool.

Was This Helpful?

Need something else? Visit the Alteryx Community or contact support.