Filter Transformation in Informatica is an active transformation, which is used to filter (or) removing the unwanted data on condition that we created using Ports, Operator and operand elements.

Filter Transformation in Informatica

Filter Transformation returns ‘True’ or ‘False’. True indicates that  an Input record meets the given condition and the record is delivered for ‘loading’. False indicates, that an input records does not meet the condition and record is delivered for ‘Reject’.

Filter Transformation with Example

In this filter transformation example, we are going to create Filter Employees who’s Salary greater than 2000. Bases on the source  data, we can filter the data. If source is a relational, we can filter the rows using source qualifier filer. If source is a flat file we use filer transformation.

Creating Filter Transformation Mapping

To create Mapping for any transformation, source and target must be selected. Here our source is EMP and Target is T_EMP.

Drag and drop source table and target table from repository navigator window to workspace window. When a source  table is inserted in the workspace, automatically SQ_EMP is inserted. SQ means source qualifier.

Creating Filter Transformation

Navigate to transformation tab to create filter transformation. A new window will be opened with two options, select transformation type as Filter and enter the name for transformation.

Adding filter condition.

In this example, our main purpose is to filter employees whose salary >= 2000. Select the required fields from SQ_EMP and drop those fields into Filter transformation.

What is Filter Transformation and how to create
What is Filter Transformation and how to create

Now Double click on the filter transformation header and click on properties tab to add filter condition using expression editor. In this expression editor, select ports tabs, double click on SAL and enter condition as SAL <= 2000 as shown below. 

  • Click on validate to check the condition is valid or not and finally apply the condition.

Adding Port from Filter Transformation to Target Table

Already we have added target table from navigator to workspace window, select required ports from the filter transformation and drop the ports into target table (T_EMP) as shown below.

What is Filter Transformation and how to create
  • Go to repository menu to Save.

Creating Workflow and Session for Filter Transformation

Now we are going to create filter workflow for our transformation. To create workflow in Informatica PowerCenter, open Informatica PowerCenter Workflow manager.

  • Navigate to tools | Workflow designer | Create and enter the name for workflow.

Now our main step is to create a session for our transformation. Session in Informatica is a set of instruction which defines how and when to move the data from Source definition to target definition. To create a session, go to tasks | click on create. 

What is Filter Transformation and how to create

Enter a name for your session and connect with workflow using link task a shown below.Now we are going to configure mapping for our session by editing session. Double click on session and navigate to Mappings tab.

What is Filter Transformation and how to create
  • Click on Connection and select the value as shown below.
What is Filter Transformation and how to create
  • Now select database connection for source and targets. In target, change target load type form build to normal and finally click on Ok button.
What is Filter Transformation and how to create
  • Go to workflow and click on validate to check our session is valid or not.
  • Save the workflow settings.

Run Filter Transformation Workflow

To run a workflow in Informatica PowerCenter, navigate to Workflow and click on start workflow. Now our workflow will be executed and the status will be monitored in Workflow monitor. Open Informatica PowerCenter Workflow monitor to check workflow is executed successfully or not.

What is Filter Transformation and how to create

As shown above, in source table we 14 rows, after applying filter transformation with condition SAL >= 2000, 6 rows has been loaded successfully into target table and no row is rejected.

Check Filter Transformation Output in SQL Developer

Open SQL developer in your local machine and open target table as shown below.

What is Filter Transformation and how to create