Filter Transformation

Filter transformation is an active & connected transformation. As an active transformation, Filter 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 those rows that return TRUE pass through this transformation. Other rows discarded do not appear in the session log or reject files/bad file.

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. The Filter transformation does not allow setting output default values.

Creating Filter Transformation:

Follow the below steps to create a filter transformation:-

In the mapping designer, open a mapping or create a new mapping.

Go to the toolbar->click on Transformation->Create->Select the filter transformation

Enter a name->Click on create and then click on done.

You can add ports either by dragging from other transformations or manually creating the ports within the transformation.

Specifying Filter Condition:

To configure the filter condition, go to the properties tab and in the filter condition section open the expression editor. Enter the filter condition you want to apply. Click on validate button to verify the syntax and then click OK.

Components of Filter Transformation:

The filter transformation has the following components.

Transformation: You can enter the name and description of the transformation.

Ports: Create new ports and configure them

Properties: You can provide filter condition to filter the rows. You can also configure tracing levels.

Metadata Extensions: Specify the metadata details like name, data type.

Configuring Filter Transformation:

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

Port name: Enter the name of the ports created.

Port type: All the ports in filter transformation are input/output.

Data type, precision, and scale: Configure the data type and set the precision and scale for each port.

Filter Transformation examples:

Create a mapping to load the employee’s details into the target who has join organization after Dec-2012?

Filter condition: HIREDATE >=’01-01-2013’

It will reject employee’s details other than who has joined organization after ‘31-Dec-2012’

Performance Tuning Tips:

    1.   Use the filter transformation as close as possible to the sources in the mapping. This will reduce the number of rows to be processed in the downstream transformations.

    2.   In case of relational sources, if possible use the source qualifier transformation to filter the rows. This will reduce the number of rows to be read from the source.

Note: The input ports to the filter transformation must come from a single transformation. You cannot connect ports from more than one transformation to the filter Transformation.