Multiple Lookup with a multiple reject strategy

algfr · Post by **algfr** » Tue Jul 21, 2009 3:55 am

Hello people,

I would like to discuss with you the best strategy for having multiple lookup with multiple reject links. i think many of us have this issue so I found useful to discuss it.

Here is what I have done and propose :

I have a primary link with N keys that I have to check (on reference table for each key).

So what I did is a lookup stage with N reference links for each table. But I want to capture, in case of a reject, the reason of the rejection (knowing which reference link "crashed"). The lookup stage only handles one reject !

The only way I found to do this is, in to load in the output link a field from each reference field and to set the lookup condition to continue everywhere. There, if the lookup failed for one particular link, the output field will be set to NULL.

There, using a Transformer stage, I can capture that and send bad records to different links depending on the condition.

Is it what peaople usually do ? Is it okay to use a Transformer stage (no efficient ?).

Thanks

jcthornton · Post by **jcthornton** » Tue Jul 21, 2009 8:50 am

That is the strategy that I have used too.

The only other method I have come up with that makes it possible to determine which specific lookup failed is to have a series of lookup stages - with one reference link per lookup stage.

The reduction in stages and the increased flexibility of using the 'Continue' setting makes your scenario my preferred method.

algfr · Post by **algfr** » Tue Jul 21, 2009 9:03 am

jcthornton wrote:That is the strategy that I have used too.

The only other method I have come up with that makes it possible to determine which specific lookup failed is to have a series of lookup stages - with one reference link per lookup stage.

The reduction in stages and the increased flexibility of using the 'Continue' setting makes your scenario my preferred method.

Excellent, JcThornton, this makes me feel good.

To do the link splitting, do you use a Transformer or a Filter stage ?

jcthornton · Post by **jcthornton** » Tue Jul 21, 2009 9:53 am

It depends on the complexity of the error handling, the expectation for changes in the future and the expected data volume.

It may not be the best practice, but I tend to use a transformer more often than a filter.