Filter
The Filter transformation component lets you filter a dataset and return rows according to criteria of your choice. You can combine multiple filter criteria using AND or OR logic.
This component is equivalent to writing a SELECT WHERE query.
Use case
This component can be used to narrow down the data you are processing, so you can focus on what is most relevant. For example, you can use it to:
- Filter a financial dataset by transaction value to see your highest-value orders.
- Filter recent customer interactions by date to analyze customer behavior within the past 30 days.
- Filter customer data by different criteria so you can target different customer groups with tailored ads.
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$
- Input Column: SCORE
- 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 |
+-------------------+------+-------+------------+