Skip to main content

Filter Tool Icon Filter Tool

One Tool Example

Filter has a One Tool Example. Visit Sample Workflows to learn how to access this and many other examples directly in Alteryx Designer.

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.

Configure the Tool

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 selected 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 setup

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 or 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 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.