How do I Exclude matching rows in two flat files usign Informatica?

Question

I have a flat file (source) with data that looks like this :
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.

If anyone can tell what transformations should I use, that would be a great help.

Answer 1

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

Answer 2

As I understood your question you want to passed the unmatched rows from your first source file.
There are various ways to do -
One method is already answered.
You can also use joiner tranformation to join then and then use Expression & Filter Tranformation to identify the unmatched record.