topic Re: Implementing complex join in Talend Studio

Implementing complex join

_AnonymousUser — Sat, 16 Nov 2024 13:19:35 GMT

Hi!
I'm pretty new with talend and I hit a problem. I dont see how I can implement the solution so here it goes...
In a normal lookup, you left join on a key and return several columns. To acheive my goal, i've to do the exact opposite : join on several non key column and return the key if there is a match. In other word, I want to find a pattern an extract the key if it exist. On one side you have several transactions (with several properties) to process. On the other you have several transaction pattern (with several propeties) and the goal is to find one patten that fit each transactions.
The SQL to accheive this is fairly simple:

select t.fk_transaction_code,
       s.fk_system,
       s.fk_customer,
       s.fk_partner
from   source.v_mapping_source s
       left join ods.v_mapping_target t on (s.fk_system   = t.fk_system            OR t.fk_system            = 0)
                                       and (s.fk_function = t.fk_function          OR t.fk_function          = 0)
                                       and (s.fk_customer = t.fk_netsuite_customer OR t.fk_netsuite_customer = 0)
                                       and (s.fk_partner  = t.fk_netsuite_partner  OR t.fk_netsuite_partner  = 0)

-The left join itself ensure a result for all row in the source
- The OR on each part of the join ensure that if a match is not found, use the default value for the property (wich is zero).
If I put in my join something like:

       left join ods.v_mapping_target t on s.fk_system   = t.fk_system
                                       and s.fk_function = t.fk_function
                                       and s.fk_customer = t.fk_netsuite_customer
                                       and s.fk_partner  = t.fk_netsuite_partner

I would only hit a result when all 4 propeties were set. the OR allow me to hit a result if one or more property is not set.

Re: Implementing complex join

_AnonymousUser — Mon, 02 Aug 2010 19:12:28 GMT

My question is how to modify my tmap to hendle these OR clause in the lef join.
I'm going to look for the proper way to add an image of my tmap.

Re: Implementing complex join

manueld — Mon, 02 Aug 2010 19:23:42 GMT

My question is how to modify my tmap to hendle these OR clause in the lef join.
I'm going to look for the proper way to add an image of my tmap.

I put a red square in SQL would be :

row6.fk_partner = row17.fk_netsuite_partner.

What should I change in the tmap to have

row6.fk_partner = row17.fk_netsuite_partner OR row17.fk_netsuite_partner = 0

instead?
Any help would be appreciated.
Sorry for my english. Not my first language!

Re: Implementing complex join

Anonymous — Tue, 03 Aug 2010 00:30:34 GMT

one not-so elegant solution would be to do the join without the OR in the tmap, and then in the inner join reject output table apply an output filter that checks for the zero values. you can then recombine the flows or simply use two different components to do all your inserts.

Re: Implementing complex join

alevy — Sun, 08 Aug 2010 04:35:57 GMT

Instead of joining those fields directly in the lookup (e.g. your red square), activate the expression filter for the lookup (white arrow with green plus) with the following:

(row6.fk_system==row17.fk_system || row17.fk_system==0)
&& (row6.fk_function==row17.fk_function || row17.fk_function==0)
&& (row6.fk_customer==row17.fk_netsuite_customer || row17.fk_netsuite_customer==0)
&& (row6.fk_partner==row17.fk_netsuite_partner || row17.fk_netsuite_partner==0)

Re: Implementing complex join

manueld — Mon, 09 Aug 2010 18:53:38 GMT

This is great. With this solution, you maid me understand so much stuff about the tmap.
Thanks a lot!!!