Lookup failing for partial matched records

Nagasudheerkumar · Post by **Nagasudheerkumar** » Thu May 21, 2015 3:37 pm

My Job Design:

                  ODBC CONNECTOR(B)          
                         |                                            
ODBC CONNECTOR(A)--->LOOKUP1---> MATCHED1   ODBC CONNECTOR(B)
                       |           |          |                                                                
                   UNMATCHED1  FUNNEL1 --->LOOKUP3--->ODBC CONNECTOR(C)
                       |            |         |
 ODBC CONNECTOR(B)-->LOOKUP2--> MATCHED2   UNMATCHED3
                        |                     |
                  UNMATCHED2 ----------->FUNNEL2--------> ODBC CONNECTOR(D)

I have a columns in A table with docnbr(2008 939030) and docnbr1(2008-939030) and Table B with jrnnbr(2008 939030) jrnnbr1(2008-939030) columns this is Or condition so I am doing lookup twice with LOOKUP1 and LOOKUP2.

LOOKUP1: docnbr=jrnnbr char(20) nullable Y=N
LOOKUP2: docnbr1=jrnnbr1 char(20) nullable Y=N

Some Values are not matching from LOOKUP1 and LOOKUP2.

LOOKUP3: Eiand=level char(255),char(2) nullable Y=Y

Here condition is Ereplace(Ereplace(link.Eiand,'oeiro','F',0,-1),(link.'kdojke','G',0,-1) and doing a lookup.

These 3 lookups are not matching for some values when comparing between A and B tables.

I have not enabled multiple rows returned from the link and partitioning is HASH on both links which is having same record count 4500.

I could see warning in logs as Ignoring for Recod 8,9 etc. May be this is because of duplicates? as my 3 Key fields contain duplicate values in both A and B tables.

Could anyone let me know how to solve this issue?

ray.wurlod · Post by **ray.wurlod** » Thu May 21, 2015 4:45 pm

Where (in which stage) is this expression with Ereplace() functions being used, and what value do you expect that it is returning?

Nagasudheerkumar · Post by **Nagasudheerkumar** » Thu May 21, 2015 11:43 pm

Sorry that i have mentioned a wrong code. Right code is this.

Code: Select all

Ereplace(Ereplace(link.Eiand,"oeiro","F",-1,0),"kdojke","G",-1,0)

I am giving this Ereplace Condition in LOOKUP3 and if link.Eiand = oeiro then 'F' or kdojke then 'G' because from LEVEL column i am getting only 'F' and 'G' values. I hope this makes sense. To do a lookup on both the columns i am using this condition before doing Lookup3.

Nagasudheerkumar · Post by **Nagasudheerkumar** » Mon May 25, 2015 4:25 pm

Any updates for me Ray?

ray.wurlod · Post by **ray.wurlod** » Mon May 25, 2015 8:00 pm

You should not be using these expressions in the Condition for a lookup; they do not generate a true/false value. Instead, generate the value in an upstream Transformer stage, and use the transformed value as your lookup key.

Nagasudheerkumar · Post by **Nagasudheerkumar** » Tue May 26, 2015 2:18 pm

Thanks Ray.. the logic is working..