Perform Lookup in tMap

[Rohitash]

Hi Team,

I have a strange requirement in my design. 

Consider, I have 2 file one is main file and another is lookup file.

Design Model 

File A -----> Tmap  -----> Output

                        |

                     File B

File A

ColA | ColB

    X   | Y

     X  | null

   null | Y

   null | null

File B

ColA | ColB | ColC

    X   | Y       | 1

     X  | #       | 2

      #  | Y      | 3

      # l #       | 4

Note : # is a wildcard, it can be any value or null value

Output expectation:

ColA | ColB | ColC

     X  | Y       | 1

     X  | null    | 2

  null  | Y      | 3

   null l null      | 4

Kindly assist, how to perform this lookup/ join  in talend.

[David_Beaty]

Hi,

I dont think you can do this in one single tMap as you are introducing a priority. You'll probably need to do it as 3 or 4 steps.

1. FileA ->tMap with FileB that inner join on Col A & B, matches go to FileC, capture the inner join rejects to File D (same schema as FileA)
2. FileD ->tMap with FileB that inner join on Col A only, matches append to FileC, capture the inner join rejects to File E (same schema as FileA)
3. FileE ->tMap with FileB that inner join on Col B only, matches append to FileC, capture the inner join rejects to File F (same schema as FileA)
4. FileF ->tMap with FileB that inner join on Col A & Col B being # , matches append to FileC should be no rejects

[David_Beaty]

Hi,

I dont think you can do this in one single tMap as you are introducing a priority. You'll probably need to do it as 3 or 4 steps.

1. FileA ->tMap with FileB that inner join on Col A & B, matches go to FileC, capture the inner join rejects to File D (same schema as FileA)
2. FileD ->tMap with FileB that inner join on Col A only, matches append to FileC, capture the inner join rejects to File E (same schema as FileA)
3. FileE ->tMap with FileB that inner join on Col B only, matches append to FileC, capture the inner join rejects to File F (same schema as FileA)
4. FileF ->tMap with FileB that inner join on Col A & Col B being # , matches append to FileC should be no rejects