Home

Filter Transformation in Informatica IICS – IDMC

Last Updated: 02 November 2023

By: Nataraj Virupaksham (Raj)

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)

Comments (1)

  1. good understanding sir

Leave a comment

Subscribe for material notification

Fill this form to get notify when we post new study materials on Raj Informatica.

Post Notification
Back to Top
Product has been added to your cart

Continue Reading Filter Transformation in Informatica IICS – IDMC

Fill out this form to explore all our interview question sets.

Question-Popup