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 ?
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
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.