Filter Transformation in Informatica

  • Updated
  • Posted in Programming
  • 3 mins read


Filter transformation is an lively, related transformation. The filter transformation is used to filter out rows in a mapping. As the filter transformation is an lively transformation, it might change the variety of rows handed by way of it. You should specify a filter situation in the filter transformation. The rows that meet the required filter situation are handed to different transformations. The rows that don’t meet the filter situation are dropped.

Creating Filter Transformation

Follow the under steps to create a filter transformation

  1. In the mapping designer, open a mapping or create a brand new mapping.
  2. Go to the toolbar->click on on Transformation->Create->Select the filter transformation
  3. Enter a name->Click on create after which click on on executed.
  4. You can add ports both by dragging from different transformations or manually creating the ports inside the transformation.

Specifying Filter Condition

To configure the filter situation, go to the properties tab and in the filter situation part open the expression editor. Enter the filter situation you wish to apply. Click on validate button to confirm the syntax after which click on OK.

Components of Filter Transformation

The filter transformation has the next parts.

  • Transformation: You can enter the title and outline of the transformation.
  • Ports: Create new ports and configure them
  • Properties: You can specify the filter situation to filter the rows. You may configure the tracing ranges.
  • Metadata Extensions: Specify the metadata particulars like title, datatype and so forth.

Configuring Filter Transformation

The following properties must be configured on the ports tab in filter transformation

  • Port title: Enter the title of the ports created.
  • Datatype, precision, and scale: Configure the information kind and set the precision and scale for every port.
  • Port kind: All the ports in filter transformation are enter/output.

Performance Tuning Tips

  • Use the filter transformation as shut as attainable to the sources in the mapping. This will cut back the variety of rows to be processed in the downstream transformations.
  • In case of relational sources, if attainable use the supply qualifier transformation to filter the rows. This will cut back the variety of rows to be learn from the supply.

Note: The enter ports to the filter transformation mush come from a single transformation. You can’t join ports from a couple of transformation to the filter.

Filter Transformation examples

Specify the filter circumstances for the next examples

1. Create a mapping to load the staff from division 50 into the goal?

department_id=50

2. Create a mapping to load the staff whose wage is in the vary of 10000 to 50000?

wage >=10000 AND wage <= 50000

3. Create a mapping to load the staff who earn fee (fee shouldn’t be null)?

IIF(ISNULL(fee),FALSE,TRUE)

Recommended Reading:


SQL Transformation in Informatica
Informatica Scenario questions
Lookup Transformation in Informatica
Router Transformation in Informatica
Update Strategy Transformation in Informatica

If you want this text, then please share it or click on on the google +1 button.

Leave a Reply