Filter
The filter node is used to filter a table using a specified column. You may filter the table by a static value or a variable.
Filtering is an important part of the data cleansing process often required for optimization of the data set to be queried by end users. The Filter function allows you to load only a specified subset of data into the target, based on the given filter expression.
Configure the Filter Node
Connect the Filter node to the Select node representing the relevant table. Go to the Properties panel to configure the node:
- Filter Column Name: select the column by which the table will be filtered.
- Filter Type: choose the type of filter from the drop-down (see below for details).
- Filter Value: select either Static or Variable:
- Static: enter a static numeric or string value.
- Variable: select the required variable; this can be a static or SQL variable.
- Case Sensitive: depending on the data type of the filter column you may be able to filter by a string (unless the filter type is set to search for nulls or blanks). If filtering by a string, you can set the filter as case sensitive is required.
Filter Types
- = Equal To: returns the rows in the selected column that are equal to the given filter value.
- <> Not Equal To: returns the rows in the selected column that are not equal to the given filter value.
- > Greater Than: returns the rows in the selected column that are greater than the given filter value.
- >= Greater Than or Equal To: returns the rows in the selected column that are greater than or equal to the given filter value.
- < Less Than: returns the rows in the selected column that are less than the given filter value.
- <= Less Than or Equal To: returns the rows in the selected column that are less than or equal to the given filter value.
- LIKE: returns the rows in the selected column that are like the given filter value, enabling the use of wild cards.
- NOT LIKE: returns the rows in the selected column that are not like the given filter value.
- BETWEEN: returns the values that are between the 2 given values.
- NOT BETWEEN: returns the values that are not between the 2 given values.
- IN LIST: enter a comma-delimited list of values; values in the list will be returned.
- NOT IN LIST: enter a comma-delimited list of values; values not in the list will be returned.
- IS NULL: returns rows containing nulls in the selected column. No filter value input is required.
- NOT NULL: returns rows not containing nulls in the selected column. No filter value input is required.
- IS NULL OR BLANK: returns rows containing null or blank cells in the selected column. No filter value input is required.
- NOT NULL OR BLANK: returns rows not containing null or blank cells in the selected column. No filter value input is required.
![Closed](../../../../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
In this example, the table is filtered to return rows in which the value in the Age column is between 30 and 45 only:
![Closed](../../../../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
In this example, the table is filtered to return only those rows in which the value in the Marital Status column matches the string 'Married':