Lookup Issue

Praful · Post by **Praful** » Fri Nov 15, 2013 11:33 am

I am facing the issue with lookup . I am doing lookup on dataset with other dataset matching three key values. If any one of the three keys are matched then the data will be processed to next level else goes into reject file. The issue is for some values even I gets the match with key value then also they are going into reject file. The partitioning on lookup is entire for all three links and partition on input dataset is hash. There are no conditions used.

Below is the job design,

Dataset -->Join-->Filter-->Lookup-->dataset

Let me know how to resolve the issue if anyone has faced this kind of problem. Thank you.

srinivas.g · Post by **srinivas.g** » Fri Nov 15, 2013 11:47 am

Use lookup condition like ((main.key1=ref.key1) or (main.key2=ref.key2) or (main.key3=ref.key3))....

prasson_ibm · Post by **prasson_ibm** » Fri Nov 15, 2013 1:50 pm

You have three reference links in lookup or you have one ref. Link with three key columns to join between input link and reference link.

I assume second situation, if this is the case,check the input and ref kry colums datatypes, it should be same.

Praful · Post by **Praful** » Fri Nov 15, 2013 4:21 pm

I checked the datatypes which is exactly same VARCHAR 255.

Input dataset (main link) have three columns (A,B,C)
Reference dataset contains one column (X).
Using copy stage splitting reference column into three links and matching with input 3 columns.

Currently there is no lookup condition. If we add below condition then it will work or not?

((main.key1=ref.key1) or (main.key2=ref.key2) or (main.key3=ref.key3))

Please suggest.

ray.wurlod · Post by **ray.wurlod** » Fri Nov 15, 2013 7:38 pm

You can't make those conditions on the lookup since you don't have the ref keys when that decision has to be made. You can, however, construct such a test once all the lookups have been attempted.

Praful · Post by **Praful** » Sun Nov 17, 2013 9:51 pm

Some records are going into reject file even if values are match with key value in Lookup stage. How should I get the rejected records into output link. Can you please give some more input which test you want me to perform after lookups completed.

prasson_ibm · Post by **prasson_ibm** » Mon Nov 18, 2013 2:58 am

Try to test with three different lookups for three joins.

crystal_pup · Post by **crystal_pup** » Tue Nov 19, 2013 1:11 am

Hi Praful

For the values that have a match but still are going into the reject link, try trimming the data in both the links ( input and reference ).

We faced same scenario wherein both the key columns had matching data type as well as length, still the records were getting rejected.We trimmed the data on the key columns and it worked.

Cheers
KD