How do I Exclude matching rows in two flat files using Informatica?
I have a flat file (source) with data that looks like this (normally a lot more data):
1,2,3
4,5,6
And a second flat file (source) that look like this:
1,2,3
I want result (target) to be a flat file that looks like this:
4,5,6
I know in SQL there is an EXCLUDE (opposite of INTERSECT) that would do the job. But I am doing this all with flat files and Informatica.
I am pretty new to Informatica. I have only been doing it for about a month. So it might be rather obvious. If you could tell me what Tranformations you would use that would be great.
You can use the union transformation to union the result sets from both the files. This would give you the following result set .
col1, col2, col3
-----------------
1,2,3
4,5,6
1,2,3
After this I would use an aggregator transformation and select these ports (and which ever you want to group by to consider them duplicate) and get the count in the aggregator transformation.
After Aggregator (new column count => total_count)
----------------------------------------------------
col1, col2, col3,total_count
-----------------------------
1,2,3,2
4,5,6,1
The next step is simple, Pass them through a filter transformation and use the following filter condition to ignore rows which have duplicates.
total_count = 1