How to implement Unconnected Lookup in Talend

[_AnonymousUser]

Hi All,
I was wondering if anyone knows how to implement Informatica's "Unconnected Lookup" in Talend. Basically what I want is to have a function such as LOOKUP_ITEM(item_id) and get the item_name returned to me or null if there's no corresponding data in the lookup table.
Thanks,
Josephine

[Anonymous]

Hi
In Talend, do a left outer join on tMap.
Shong

[Anonymous]

Hi Shong,
I think I was not able to explain myself better.. I'm really after a function-like capability that uses the current DB connection. Such that if I have row1.item_id, i can use a function in the tmap output to return the item name, i.e. lookup_item(row1.item_id) without having to create a 'Lookup table' mapping using left outer join in tMap..
Please see my screenshot.
For reference, the same functionality has been mentioned by psm2000 in https://community.talend.com/t5/Design-and-Development/tMap-next-generation-Talend-community-feedbac....
Below is an excerpt of his comment on this topic..

..............
Comment 2:
This new lookup functionality is nice. I also use Informatica that has this component called "Unconnected Lookup" that can be called from any expression. So I can define a look up on a table or a file, say a Customer table in Oracle. Then from any expression, I can call this lookup using a function such as LOOKUP_CUST(CUST_ID) and get one column returned to me or NULL. I find this very useful as I can call it in an IF/THEN condition and it can be cached/non-cached. For large tables where one is not expecting a bunch of hits, this is very efficient.
......

[Anonymous]

Hi
Talend offers a more flexible solution, you can define this kind of function by yourself, go to repository-->code-->routine and create custom routine, and then call it on tMap.
Shong

[Anonymous]

Thanks Shong, I'll try creating a routine..

[Anonymous]

Hi,
I think you have to use tmap. In tmap, join two incoming Tables them with the help of some common key & take outputs according to your choice.
Note:- First join Main table & then lookup table.
Thanks.