Skip to content

Filter

Filters rows from the input to pass a subset of rows to the next component based on a set of conditions, by generating a WHERE clause.

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 Snowflake documentation for a complete list of what Snowflake 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

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. See the Snowflake documentation.
  • ILike: Case-insensitive match of the value in the Input Column and the pattern in the Value Column. See the Snowflake 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 Column: 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.