Filter transformation – Part 1
Use the Filter transformation to filter out rows in a mapping. As an active transformation, the Filter transformation may change the number of rows passed through it. The Filter transformation allows rows that meet the specified filter condition to pass through. It drops rows that do not meet the condition. You can filter data based on one or more conditions.
A filter condition returns TRUE or FALSE for each row that the Integration Service evaluates, depending on whether a row meets the specified condition. For each row that returns TRUE, the Integration Services pass through the transformation. For each row that returns FALSE, the Integration Service drops and writes a message to the session log.
# Its an active and connected transformation.
# The filter condition is an expression that returns TRUE or FALSE.
# Any expression that returns a single value can be used as a filter.
# The numeric equivalent of FALSE is zero (0) – Drop all the records.
# The numeric equivalent of TRUE is ANY NON ZERO value. – Pass through the transformation.
# If the filter condition evaluates to NULL, the row is treated as FALSE.
Lets create a basic mapping with a Filter Transformation
Default condition for Filter Transformation is TRUE.
TRUE can also be written as any non zero value (1,2,9,99,999 … ).
TRUE will process all records and pass through the transformation.
FALSE will drop all the records from Processing and load nothing to target.
FALSE can also be written as ZERO.
We can specify multiple components of the condition, using the AND and OR logical operators.
Here is more examples to try with …
DEPARTMENT_ID=90
DEPARTMENT_ID=100 OR JOB_ID=’PU_CLERK’
DEPARTMENT_ID=90 AND JOB_ID=’AD_VP’
DEPARTMENT_ID != 100
(DEPARTMENT_ID=60 OR DEPARTMENT_ID=100) AND SALARY > 8000
DEPARTMENT_ID=500
We don’t have any Department ID 500 in the source table and hence this expression will be evaluated as FALSE. So nothing will be loaded to target.
We will cover more Filter Conditions using Expression in next session.