Filter Transformation in Informatica IICS – IDMC

Filter transformation is an active and connected transformation in Informatica Cloud IDMC. It is used to filter data out of the data flow based on a specified filter condition.

• ▹ The filter condition defined in the filter transformation is an expression that returns either TRUE or FALSE.
• ▹ The default return value from filter transformation is TRUE.
• ▹ You can define filter condition as FALSE which acts as a logical stop of the flow of the mapping as no records will be passed further.
• ▹ In Filter transformation, filter conditions can be defined in three different types Simple, Advanced, Completely Parameterized.

Simple Filter Condition:

• ▹ You can create one or more simple filter conditions. A simple filter condition includes a field name, operator, and value.
• ▹ When you define more than one simple filter condition, the mapping task evaluates the conditions in the order that you specify using the AND logical operator.
• ▹ In the below example, the filter condition will be treated as LOCATION_ID > 10 and CITY = ‘Bangalore’

Advanced Filter Condition:

• ▹ You can use an Advanced filter condition to define a complex filter condition.
• ▹ When you configure an advanced filter condition, you can incorporate multiple conditions using the AND or OR logical operators.
• ▹ When you change the filter condition type from simple to advanced, the Mapping Designer automatically converts the simple filter conditions to the advanced filter condition.

Few Examples of Filter Conditions:

• ▹ SALARY > 30000
• ▹ SALARY > 30000 AND SALARY < 100000
• ▹ IIF(ISNULL(FIRST_NAME), FALSE, TRUE)
• ▹ To filter rows containing null values or spaces, use the ISNULL and IS_SPACES functions to test the value of the port. For example, if you want to filter out rows that contain NULL value 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 Filter Transformation Examples
• ▹ The numeric equivalent of TRUE is ANY NON-ZERO value. Pass through the transformation.
• ▹ TRUE can also be written as any non-zero value (1,2,9,99,999 …).
• ▹ The numeric equivalent of FALSE is zero (0) Drop all the records
• ▹ To DEPARTMENT_ID=90
• ▹ DEPARTMENT_ID=100 OR JOB_ID=’PU_CLERK’
• ▹ DEPARTMENT_ID=90 AND JOB_ID=’AD_VP’
• ▹ DEPARTMENT_ID! = 100
• ▹ (DEPARTMENT_ID=60 OR DEPARTMENT_ID=100) AND SALARY > 8000
• ▹ NOT ISNULL(STATE) AND NOT ISNULL(POSTAL_CODE)