How to achieve ‘NOT EXISTS’ functionality during Data Load in Informatica?

Informatica is a worldwide tool used for Data cleansing and for loading data into Datawarehouse. It provides us with a lot many features to load the customer data and then work on it based on our requirements. However occasionally we come across such situations wherein, we must build certain logics which are not straightforward and require us to think on how to use the transformations to achieve that logic. We can use the built-in functions and the transformations in many ways to develop various simple and complex logics.

‘NOT exists’ is one of the functorially which we can achieve by just following a simple logic. So, lets start on how this can be done.

Lets, assume that we have two flat files A and B. Requirement we have is we need all the records from flat file A which do not exist in file B. Hence, we need to achieve NOT exist functionality here.

All the rows from A which are not in B.

All the rows from A which are not in B.

Data in Flat file A and B

So, we need the output to contain

ID, Name, Salary

1, Nikhil,10000

2, Shivam,20000

5, Rahul,40000

  • Firstly, join the two flat files using joiner transformation and use ‘DETAIL OUTER JOIN’. Make file A as master. This will give you all the rows from file A and only the matching rows from file B.

Imp – Make sure File A is used as Master on using Detail outer Join. In detail outer join returned from the all rows from the master source are returned and only matching rows from the detail source.

  • Now, map the data coming from joiner to expression transformation and do a lookup on file B to look for all the records which exists in file B and flag them as 0 and that do not exist to flag them as 1.

This can be easily achieved by using the lookup on id and checking it is NULL or not. If NULL then match is not found and we can flag the row as 1 and in case if id is returned then we need to flag those rows as 0.

  • Now simply use the filer transformation to remove the records with flag 0 and add the rest of the data is moved to the target.

So, by just following the above steps you can easily achieve NOT exists function in Informatica.

Be the first to comment

Leave a Reply

Your email address will not be published.