Let see what the official documentation says about Filter Transformation:
“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. The Filter transformation is an active transformation.
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.”
Filter Transformation simple example
We have already seen in Source Qualifier that it is possible to filter your source using the Source Qualifier itself, however it’s not always the case when your source is a SQL source, sometimes it’s an Excel spreadsheet or flat file, or something else that do not run on SQL. For those cases, make use of Filter Transformation.
In this example I’m getting our customer table and filtering only those who are Sales Representative. Remember, Filter Transformation is case sensitive. So, you might want to make sure that everything is in upper or lower case before you perform a filter.
To apply the Filter we just drag our source and target, add a filter transformation, and connect everything, like in the image below.
After that, we set up the filter itself. We do that by editing the transformation > going to Properties tab > tinker with the Filter Condition option.
This is my filter condition. A pretty simple one:
And here is the result after this mapping has runned. 17 rows only. The original table has 99 rows.
Filter Transformation details
You cannot concatenate ports from more than one transformation into the Filter transformation. The input ports for the filter must come from a single transformation. It means that, if you have another source, like a flat file, you will not be able to join, to drag, those attributes from the flat file to the filter transformation. See the image below.
As you can see, there is no way to move income from our flat file source to the filter transformation, which is already being used by attributes from our SQL source.
Filter condition
The filter condition is an expression that returns TRUE or FALSE. Meaning that all rows are going to be evaluated by powercenter, and only those who return TRUE pass from the filter to the target, and all rows that return FALSE will be dropped and not registered.
Any expression that returns a single value can be used as a filter. You can specify multiple components of the condition, using the AND and OR logical operators, and many other operators – see the full list of operators in the official documentation.
You can also enter a constant for the filter condition. The numeric equivalent of FALSE is zero (0).
Filtering Rows with NULL values
To filter rows containing null values or spaces, use the ISNULL and IS_SPACES functions to test the value of the port. For example, if you want to filter out rows that contain NULL value in the FIRST_NAME port, use the following condition:
IIF(ISNULL(FIRST_NAME),FALSE,TRUE)
This condition states that if the FIRST_NAME port is NULL, the return value is FALSE and the row should be discarded. Otherwise, the row passes through to the next transformation.
Important Tips for Filter Transformation
- Use the Filter transformation early in the mapping
To maximize session performance, keep the Filter transformation as close as possible to the sources in the mapping. Rather than passing rows that you plan to discard through the mapping, you can filter out unwanted data early in the flow of data from sources to targets.
- Use the Source Qualifier transformation to filter
The Source Qualifier transformation provides an alternate way to filter rows. Rather than filtering rows from within a mapping, the Source Qualifier transformation filters rows when read from a source. The main difference is that the source qualifier limits the row set extracted from a source, while the Filter transformation limits the row set sent to a target. Since a source qualifier reduces the number of rows used throughout the mapping, it provides better performance.
However, the Source Qualifier transformation only lets you filter rows from relational sources, while the Filter transformation filters rows from any type of source. Also, note that since it runs in the database, you must make sure that the filter condition in the Source Qualifier transformation only uses standard SQL. The Filter transformation can define a condition using any statement or transformation function that returns either a TRUE or FALSE value.
Pedro Carvalho 
One thought on “Informatica Powercenter – Filter Transformation”