[Informatica] Filter Transformation

Filter Transformation

::Transformation type::
Connected
Active

::Use::
We pass all the rows from a source transformation through the Filter transformation, and then enter a filter condition for the transformation. The Filter transformation allows us to filter rows in a mapping.  Only rows that meet the condition pass through the Filter transformation. All the rows in Filter Transformation are either Input or Input/Output.
Filter being an active transformation,  may change the number of rows passed through it. A filter condition returns TRUE or FALSE for each row that passes through the transformation, depending on whether a row meets the specified condition. Only rows that return TRUE pass through.


::Filtering Condition::
You use the transformation language to enter the filter condition. The condition is an expression that returns TRUE or FALSE. For example, if you want to filter out rows for employees whose salary is less than $30,000 and more than $100,000 , you enter the following condition:
SALARY > 30000 AND SALARY < 100000
Here, only the rows for Employyes whose salary is less than $30,000 and more than $100,000 will pass through the Filter Transformation.


::Performance Improvement::
--Use the Filter transformation early in the mapping
To maximize session performance, include the Filter transformation as close to the sources in the mapping as possible. Rather than passing rows you plan to discard through the mapping, you then filter out unwanted data early in the flow of data from sources to targets. This avoid unnecessary data processing which is needless.
--Use the Source Qualifier transformation to filter::
Rather than filtering rows from within a mapping, the Source Qualifier transformation can filter rows when read from a source. 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. (Eg. Flat File etc.)


::Troubleshooting::
-The filter condition is case-sensitive.
-If a field contains additional spaces, the filter condition needs to check for additional spaces for the length of the field. Use the RTRIM function to remove additional spaces.
- The discarded rows do not appear in the session log or reject files


::Example::
The following example shows how a Filter transformation can be used to get the Employee records where salary lies between 1000 and 20000.