topic Re: Left outerjoin problem in tMap in Talend Studio

Left outerjoin problem in tMap

Anonymous — Sat, 16 Nov 2024 04:57:08 GMT

Hello,

I am trying to join between 2 CVS files with contents as below

id first_name last_name email engagement attr1
4903g34 Joe Johnson joe.johnson@spamhole.com 49 red
48982nf Mike Jackson mj@temp-mail.org 422 oil
4903g34 Sandrine Jervais sjervai@generator.email 4 linen
jh41922 Samantha Holland sam2382@mailinator .com 24 Grassy

id sex tier lastcontact pets attr1 attr2
4903g34 0 platinum 1/24/2018 0 minivan +1 569-483-2388
48982nf 0 red 1/12/2018 1 car +1 555-555-1212
an2944a 1 gold 2/17/2018 1 pickuptruck +1 432-867-5309
jh41922 1 blue 11/4/2017 0 Car +1 716-873-9837

I have joined them with tMap but I am not getting correct result for left outer join. For unmatched rows it should be selecting null but instead it selects first matched row attributes. Please let me know if anyone can help.

email tier gender first_name last_name
joe.johnson@spamhole.com platinum m Joe Johnson
mj@temp-mail.org red m Mike Jackson
sjervai@generator.email platinum m Sandrine Jervais
sam2382@mailinator .com blue f Samantha Holland

Re: Left outerjoin problem in tMap

Anonymous — Fri, 16 Aug 2019 06:06:30 GMT

Try using All joins in TMap.

Re: Left outerjoin problem in tMap

iamabhishek — Fri, 16 Aug 2019 11:46:46 GMT

I am suspecting you might have selected "First Match" as your "Match model". You would need "All rows" instead.

Re: Left outerjoin problem in tMap

JR1 — Sat, 17 Aug 2019 20:04:27 GMT

Sorry to disagree but the initial problem has nothing to do with the Match Model.

I assume the following is true:

The first table is the main input of the tMap
The second table is the lookup in the tMap
You are matching on the "id" column

When I look at the data, I can find a match in your lookup for every record in your main input. Which record did you expect to have null values? There are two records with the same id in your main input (rows 1 and 3) and they both match with record 1 in your lookup. Therefore, the short answer is: there are no unmatched records in your example.

However, if you switch "main" and "lookup" of the tMap, you will have one record unmatched (row 3 in your second table) and one record will have two matches (row 1). In this case, the Match Model will play a role:

"First match" will select the values from the first matched record (row 1 in the first table)
"Unique match" will select the values from the last matched record (row 3 in the first table)
"All matches" will duplicate the main record for each row in your lookup.

I hope this helps.

Re: Left outerjoin problem in tMap

Anonymous — Tue, 20 Aug 2019 14:09:40 GMT

Your observation is correct. I validated the result and got correct output.

Thanks

Krishanu