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 |
+-------------------+------+-------+------------+
Got feedback or spotted something we can improve?
We'd love to hear from you. Join the conversation in the Documentation forum!