Tutorial corner


How to remove null records in informatica ?

Spread the love

While developing informatica mapping , you come across various informatica scenarios where you need to skip null records while loading data. In other words you want to remove null records in informatica . First transformation comes to your mind will be filter transformation . But you may not be sure how to use filter transformation to remove null records in fitler transformation. Do we  have any other alternative way also to remove null records in informatica ?

remove null records in informatica

Business scenarios to remove null records in informatica

Before we discuss how to remove null records in informatica , lets discuss why we need to remove null records.  There can be many Informatica scenarios where you dont want to process null records in informatica mapping. For example :-

  • There  may be issue in input data ,where null  fields are not correct . Those records may need to be ignored so that it can be processed later on after correcting  those input records.
  • Processing need to be done only on not null fields. For example employee bonus needs to be calculated only when commission field is not null.

How to remove null records in informatica ?

So we discussed various informatica scenarios where need to ignore null records ,now the question is how to achieve this.

Removing null records by Source Qualifier

By using filtering condition in Source qualifier’s override SQL itself. For example  we can use below below SQL in Source Qualifier’s SQL .

select employee.id , employee.name , employee.salary, employee.comm
from employee
where employee.comm is not null

This option has two advantage here.

Removing  null records via SQL will be faster , as it is running directly on data base and can use indexing also.

It will be giving less records for informatica processing , hence improving the performance of  informatica mapping overall.

Removing null records by filter transformation

Removing records at source qualifier may not be possible ( because of file source ) or we  need to remove records in between the mappings. In such cases we need to use filter transformation. we can use ISNULL function to find out null fields.

Check here for filter transformation example in informatica

For example , we can use IIF(ISNULL(comm),FALSE,TRUE) in the filter condition.

Removing null records by Router transformation

We can also use router transformation also with default group going nowhere. We need to use ISNULL function in group condition.

Check here for Router transformation example in informatica

So here we discussed how to remove null records in informatica ? If you have any other option , please add write to us or add in comment section.

The Author


Hello Friends , I am Kajal Mishra .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme