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.
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.
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.
No comments:
Post a Comment