Capture Only Not matching rows

yugee · Post by **yugee** » Mon Mar 03, 2014 12:27 pm

Hi,

I have an input with a key and another table with error records. I have to match my input with the table and get only the non matching records. Currently I have a lookup, output and reject. But, since I don't want any matched records for further processing, I want to avoid writing these matched recors into a file. Is there any way I can capture only the Non Matching records from look up?

Input 1: File
Lookup: Table
Output: Only non matched records

Thanks in advance,
Yugee.

chulett · Post by **chulett** » Mon Mar 03, 2014 12:41 pm

Sure. Set the Lookup's "Failed lookup" rule to Continue and allow only null key columns downstream from there.

yugee · Post by **yugee** » Mon Mar 03, 2014 1:09 pm

Thanks Craig..But in my case there are 90% records matching and only 10% of them are not matching (average volume in the input is around 100K). I can have a filter stage to get only the null value records, but in this case I will be checked the null value for an extrat 90% of the records. This job is critical and any time/processing saved would be critical.

chulett · Post by **chulett** » Mon Mar 03, 2014 1:49 pm

They have to be checked one way or another so it's not like wasted processing time. Alternately, you could load the file into your database and use SQL to get the non-matches but regardless 100% of the data needs to be checked.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 03, 2014 4:11 pm

Use a Reject link on the Lookup stage to capture the failed lookups.

Direct the regular output link to a sink of some kind, such as a Copy stage with no output link.