Filtering and Using Expressions

Data Preparation in Alteryx

Deanna Sanchez

Alteryx ACE and Owner, Nova Geographica LLC

Filtering Data

  • Filtering narrows a dataset by removing data to focus on a specific subset.
    • Example: [Sale Price] >= 100000

 

Sale Price Table Filter Comparison Updated.jpg

Data Preparation in Alteryx

The concept of filtering is like . . .

Image of sculptor removing material to create art

Data Preparation in Alteryx

Filtering in Alteryx

Filter tool - available from the Favorites and Preparation toolset

There are two options:

  • Basic filter
    • One field with an operator and/or criteria

Basic Numeric Filter Expression Example

Data Preparation in Alteryx

Filtering in Alteryx

Filter tool - available from the Favorites and Preparation toolset.

There are two options:

  • Basic filter
    • One field with an operator and/or criteria

Basic Numeric and String Filter Expression Examples

Data Preparation in Alteryx

Filtering in Alteryx

Filter tool - available from the Favorites and Preparation toolset.

There are two options:

  • Basic filter
    • One field with an operator and/or criteria

Basic Filter expression examples

Data Preparation in Alteryx

Filtering in Alteryx

Filter tool - available from the Favorites and Preparation toolset.

There are two options:

  • Custom filter
    • Multiple criteria and expressions

Custom Filter AND Expression Example

Data Preparation in Alteryx

Filtering in Alteryx

Filter tool - available from the Favorites and Preparation toolset.

There are two options:

  • Custom filter
    • Multiple criteria and expressions

Custom Filter OR Expression Example

Data Preparation in Alteryx

Filtering in Alteryx

Filter tool - available from the Favorites and Preparation toolset.

There are two options:

  • Custom filter
    • Multiple criteria and expressions

Custom Filter Date Expression Example

Data Preparation in Alteryx

Filter tool outputs

Two output anchors:

  • True
    • Criteria are met

Image of Filter tool True output anchor

Data Preparation in Alteryx

Filter tool outputs

Two output anchors:

  • True
    • Criteria are met
  • False
    • Criteria are not met

Image of Filter tool False output anchor

Data Preparation in Alteryx

AND and OR

A Custom filter can use AND and OR within the criteria expression.

  • AND

    • BOTH criteria must be met
    • [Shape] = 'Triangle' AND [Letter] = 'B'

  • OR

    • EITHER criteria must be met
    • [Shape] = 'Triangle' OR [Letter] = 'B'

Image of dataset categories

Data Preparation in Alteryx

AND statement

  • BOTH criteria must be met
    • [Shape] = 'Triangle' AND [Letter] = 'B'

Assortment of shapes with letters and result of AND statement

Data Preparation in Alteryx

OR statement

  • EITHER criteria must be met
    • [Shape] = 'Triangle' OR [Letter] = 'B'

Assortment of shapes with letters and result of OR statement

Data Preparation in Alteryx

Expression syntax

  • String data require quotes (single or double quotes can be used)
    • [Month] = 'January'

String expression example

Data Preparation in Alteryx

Expression syntax

  • Numeric data do not use quotes
    • [Price] > 100

Numeric expression example

Data Preparation in Alteryx

Expression syntax

  • ! signifies "not"
    • !IsEmpty([Product])

Not statement with exclamation mark expression example

Data Preparation in Alteryx

Expression syntax

  • Be sure to check the field data type
    • [Length] = '0' (Quotes are required for string data even if the field contains a number)

String shown as number expression example

Data Preparation in Alteryx

Field syntax in custom expressions

  • Include the field name in each part of a multi-statement expression
  • Repeat the field name before the criteria in expressions

Using the same field in a custom expression example

Data Preparation in Alteryx

Formulas in the Filter tool

There are many ready-to-use formulas available in the Filter tool custom filter.

  • Click the Fx button to view the formula categories
  • Same formulas as in the Formula tool

Formula tool list of formulas

Data Preparation in Alteryx

Let's practice!

Data Preparation in Alteryx

Preparing Video For Download...