Informatica Filter Transformation

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

Informatica Filter Transformation
This is the source Table Records used for this mapping.
Informatica Filter Transformation
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.

Informatica Filter Transformation
Edit the transformation and give filter condition, 999 is same as ‘TRUE’
Informatica Filter Transformation
TRUE – Pass through all source records to target.

FALSE will drop all the records from Processing and load nothing to target.
FALSE can also be written as ZERO.

Informatica Filter Transformation
FALSE- Dropped all records and loaded nothing to target.

We can specify multiple components of the condition, using the AND and OR logical operators.

Informatica Filter Transformation
Edit the transformation and give filter condition with logical operators. 
Informatica Filter Transformation
See the result, We have only Department 60 & 100 records.

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

Informatica Filter Transformation
Edit the transformation and give filter condition.
Informatica Filter Transformation
We have only department 60 & 100 with salary more than 8000 in the target table.

 
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.