TMAP lookup question

[RVeitch_84]

Looking for some help with a basic lookup

See attached screenshot.

I'm trying do do a simple lookup and return two fields if one match is found and reject if duplicate values are found

during 

the lookup.

If I'm looking up term=1 and bm=5931194 ,I need that to be pushed to the rejects output.

But if I'm looking up term=4 and

bm=5931194, I need that to go to the normal output and return 6266 for fa and ABC for ccp.

[anselmopeixoto]

Hi @Robert Veitch​ 

I suggest that before the lookup subJob you do an aggregation over id and bm columns of your lookup and create a count column to store the number of occurrences of this key. tAggregateRow component can do it for you. You can store that aggregation result in memory using a tBufferOutput or tHashOutput component.

Then, on your tMap, using a tBufferInput or a tHashInput component, add an additional lookup using the same id and bm keys.

Now you can create two outputs for tMap and use the count column to filter where the join result will be sent. If count > 1 you send the input + lookup result to the reject output and if count <= 1 you send it to the other output.