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.
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.
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:
