filter out rows with null values in informatica

Various time ,we need to filter null values in informatica. We can remove them for further processing or other scenarios also . Check Informatica scenarios of filtering out rows with null values .

To filter our rows with null values , we can use it in Source qualifier's over ride SQL or you can use  filter transformation in informatica.

Must check : Normalizer Transformation in informatica

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.

Must check : Lookup Transformation in informatica

Searches related to how to filter null values in informatica

informatica isnull filter condition
how to handle null values in informatica
informatica filter is not null
filter null values in sql
filter null values in sql server
powershell filter null values
qlikview filter null values
isnull in informatica syntax

Rank transformation in informatica

Rank Transformation in Informatica , is a connected and active /Passive transformation which select top/bottom rows of   input. It is something similar to Rank analytical data function or oracle. Only difference is that, it also filter out the remaining rows  (which are not a part of top/bottom threshold).

Must check : Please also check how to create Aggregator Transformation

How Rank Transformation works in Informatica?

It first cache all the input data and then performs the rank calculation per group , filter out the unwanted records.

Steps to create an Rank transformation:

• In the Mapping Designer, open a Mapping.
• Click Transformation > Create. Select Rank transformation.
• Enter a name and click Done.
• You will see one port RANKINDEX port already there. This port store the ranking of each   record  and can be used to populate target as well
• Add all additional port from source input which  are going to be use in following transformation.
• Open the port tab and first check the Group by option for desired column ( for example deptno in our case)
• Also  check the Rank (R) option for the port which you want to do ranking. For example salary in our case.

Must Read : Lookup Transformation in informatica

Note: We can define Group by indicator for multiple port, but  Ranking can be done   on single port only.

• Go to the properties tab, select the Top/Bottom value as Top and the Number of Ranks property as per need.
• Click OK.
• Connect output ports of Rank Transformation to other transformation or target

Must Read : Learn more about informatica coding standard

Example of Rank Transformation in Informatica:

A good example of Rank transformation with different type of join can be found here.

For more explanation on Rank transformation , you can read it here and also check here for informatica interview question on Rank transformation

Incoming Search Terms :-

rank transformation properties

rank transformation in informatica with example
rank transformation in informatica interview questions
how to implement rank transformation in informatica
rank transformation in informatica tutorial
rank transformation in informatica pdf
rank transformation in informatica ppt