Skip to content

Filter

Filter is a transformation component that takes input table data and filters out rows according to criteria set in the Filter Conditions property. The resulting output is a subset of those input rows. This is accomplished by generating a WHERE clause.

Properties

Name = string

A human-readable name for the component.

Filter Conditions = expression editor

Input Column: The name of the column from the input component to which the filter is applied.

Qualifier:Select whether matching rows in the data should be included (Is, the default) or excluded (Not).

Comparator: Select from:

  • Less than: (default) Value in the Input Column must be less than that specified in the Value Column.
  • Less than or equal to: Value in the Input Column must be less than or equal to that specified in the Value Column.
  • Equal to: Value in the Input Column must be equal to that specified in the Value Column.
  • Greater than (default): Value in the Input Column must be greater than that specified in the Value Column.
  • Greater than or equal to: Value in the Input Column must be greater than or equal to that specified in the Value Column.
  • Like: Case-sensitive match of the value in the Input Column and the pattern in the Value Column. Read the following documentation:
  • ILike: Case-insensitive match of the value in the Input Column and the pattern in the Value Column. Read the following documentation:
  • Similar to: Matches the value in the Input Column with a SQL standard regular expression pattern in the Value column.
  • Null: Checks whether the value of the Input Column is the SQL "null" value.
  • Blank: Checks whether the value of the Input Column is an empty string.
  • Null or blank: Checks whether the value of the Input Column is an empty string or the SQL "null" value.

Value: The value entered by the user to use for comparison. If this value is enclosed in double quotes, it will be treated as a column in the generated SQL, which will allow for comparing columns.

Combine Condition = drop-down

When multiple filter conditions are present, they can be separated by AND or OR.

  • AND: All the filter conditions must be true.
  • OR: Any of the Filter Conditions must be true.

For more complex conditions involving grouping several AND and OR conditions together, consider creating a new field using a Calculator component that specifies your complex condition, and then use that new field in a simple filter condition.

Working with dates

Values representing a constant date/time need to be single-quoted so that they are recognized by Snowflake. The following formats are recognized by the Filter component, and will be quoted automatically:

  • yyyy-MM-dd
  • yyyy-MM-dd HH:mm:ss
  • yyyy-MM-dd HH:mm:ss +HH:mm
  • yyyy-MM-dd HH:mm:ss.SSSSSS
  • yyyy-MM-dd hh:mm:ss.SSSSSS +HH:mm
  • yyyy-MM-ddThh:mm:ss.SSSSSSZ

Other date/time formats recognized by Snowflake will work if they are single-quoted in the Filter property, such as RFC format 'Thu, 21 Dec 2000 04:01:07 PM'.

Read the following documentation for a complete list of what the following cloud platforms will recognize automatically:

Functions and column references can also be used in this component. Values such as the following will work as expected:

  • CURRENT_TIMESTAMP()
  • "anotherDateColumn" + 365

Examples

Input Data

+-------------------+------+-------+------------+
|       EMAIL       | EXAM | SCORE |    DATE    |
+-------------------+------+-------+------------+
| tgf@gmail.com     | math |    66 | 2022-02-24 |
| toml@gmail.com    | math |    78 | 2022-01-25 |
| jenny1@gmail.com  | chem |    44 | 2021-11-05 |
| lucygh@aol.com    | chem |    70 | 2022-02-13 |
| suecool@gmail.com | math |    89 | 2022-01-29 |
+-------------------+------+-------+------------+

Finding all passing grades

We can use "Greater than" comparators on integer type columns to find values greater than a given value.

Filter component properties:

  • Filter Conditions:
    • Input Column: SCORE
    • Qualifier: Is
    • Comparator: Greater than or equal to
    • Value: 70
  • Combine Conditions: (We have a single filter so this doesn't matter)
Output Data

+-------------------+------+-------+------------+
|       EMAIL       | EXAM | SCORE |    DATE    |
+-------------------+------+-------+------------+
| toml@gmail.com    | math |    78 | 2022-01-25 |
| lucygh@aol.com    | chem |    70 | 2022-02-13 |
| suecool@gmail.com | math |    89 | 2022-01-29 |
+-------------------+------+-------+------------+

Passing grades with gmail addresses

Like, ILike and Similar to allow you to filter column values using a regular expression.

Filter component properties:

  • Filter Conditions:
    • Input Column: SCORE
      • Qualifier: Is
      • Comparator: Greater than or equal to
      • Value: 70
    • Input Column: EMAIL
      • Qualifier: Is
      • Comparator: Similar to
      • Value: \^[^@]+@gmail.com$
  • Combine Conditions: AND
Output Data

+-------------------+------+-------+------------+
|       EMAIL       | EXAM | SCORE |    DATE    |
+-------------------+------+-------+------------+
| toml@gmail.com    | math |    78 | 2022-01-25 |
| suecool@gmail.com | math |    89 | 2022-01-29 |
+-------------------+------+-------+------------+

You could instead use the Like comparator with equivalent value %gmail\.com to do the same email filtering.

Finding exams after January 2022

We can use "Greater than" comparators on date type columns to find dates after a given value. This requires that date values are in a recognized format.

Filter component properties:

  • Filter Conditions:
    • Input Column: DATE
    • Qualifier: Is
    • Comparator: Greater than
    • Value: 2022-01-31
  • Combine Conditions: (We have a single filter so this doesn't matter)
Output Data

+-------------------+------+-------+------------+
|       EMAIL       | EXAM | SCORE |    DATE    |
+-------------------+------+-------+------------+
| tgf@gmail.com     | math |    66 | 2022-02-24 |
| lucygh@aol.com    | chem |    70 | 2022-02-13 |
+-------------------+------+-------+------------+
Snowflake Databricks Amazon Redshift
✅ ✅ ✅