Filter Transformation
Filter Transformation in Informatica
Filter transformation is an active, connected transformation. The filter transformation is used to filter out rows in a mapping. As the filter transformation is an active transformation, it may change the number of rows passed through it. You have to specify a filter condition in the filter transformation. The rows that meet the specified filter condition are passed to other transformations. The rows that do not meet the filter condition are dropped.
We can filter rows in a mapping with the Filter transformation. We pass all the rows from a source transformation through the Filter transformation, and then enter a Filter condition for the transformation. All ports in a Filter transformation are input/output and only rows that meet the condition pass through the Filter Transformation.
Example: to filter records where SAL>2000
- Import the source table EMP in Shared folder. If it is already there, then don’t Import.
- In shared folder, create the target table Filter_Example. Keep all fields as in EMP table.
- Create the necessary shortcuts in the folder.
Creating Mapping:
- Open folder where we want to create the mapping.
- Click Tools -> Mapping Designer.
- Click Mapping -> Create -> Give mapping name. Ex: m_filter_example
- Drag EMP from source in mapping.
- Click Transformation -> Create -> Select Filter from list. Give name and click Create. Now click done.
- Pass ports from SQ_EMP to Filter Transformation.
- Edit Filter Transformation. Go to Properties Tab
- Click the Value section of the Filter condition, and then click the Open button.
- The Expression Editor appears.
- Enter the filter condition you want to apply.
- Click Validate to check the syntax of the conditions you entered.
- Click OK -> Click Apply -> Click Ok.
- Now connect the ports from Filter to target table.
- Click Mapping -> Validate
- Repository -> Save
Create Session and Workflow as described earlier. Run the workflow and see the data in target table.
How to filter out rows with null values?
To filter out rows containing null values or spaces, use the ISNULL and IS_SPACES Functions to test the value of the port. For example, if we want to filter out rows that Contain NULLs 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.
Performance tuning:
Filter transformation is used to filter off unwanted fields based on conditions we Specify.
- Use filter transformation as close to source as possible so that unwanted data gets Eliminated sooner.
- If elimination of unwanted data can be done by source qualifier instead of filter,Then eliminate them at Source Qualifier itself.
- Use conditional filters and keep the filter condition simple, involving TRUE/FALSE or 1/0
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 specify the filter condition to filter the rows. You can also configure the tracing levels.
- Metadata Extensions: Specify the metadata details like name, datatype etc.
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.
- Datatype, precision, and scale: Configure the data type and set the precision and scale for each port.
- Port type: All the ports in filter transformation are input/output.
Note: The input ports to the filter transformation mush come from a single transformation. You cannot connect ports from more than one transformation to the filter.
Filter Transformation examples
Specify the filter conditions for the following examples
1. Create a mapping to load the employees from department 50 into the target?
department_id=50
2. Create a mapping to load the employees whose salary is in the range of 10000 to 50000?
salary >=10000 AND salary <= 50000
3. Create a mapping to load the employees who earn commission (commission should not be null)?
IIF(ISNULL(commission),FALSE,TRUE)
No Comment to " Filter Transformation "