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 |
+-------------------+------+-------+------------+

Example 1Example 2Example 3

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
✅	✅	✅