topic Perform Lookup in tMap in Talend Studio

Perform Lookup in tMap

Rohitash — Sat, 16 Nov 2024 01:58:11 GMT

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 — Thu, 09 Jul 2020 17:40:19 GMT

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.

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)
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)
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)
FileF ->tMap with FileB that inner join on Col A & Col B being # , matches append to FileC should be no rejects

David_Beaty — Thu, 09 Jul 2020 17:40:19 GMT

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.

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)
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)
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)
FileF ->tMap with FileB that inner join on Col A & Col B being # , matches append to FileC should be no rejects