One Star

How to implement Unconnected Lookup in Talend

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
5 REPLIES
Community Manager

Re: How to implement Unconnected Lookup in Talend

Hi
In Talend, do a left outer join on tMap.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: How to implement Unconnected Lookup in Talend

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 http://www.talendforge.org/forum/viewtopic.php?id=5778.
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.
......

Community Manager

Re: How to implement Unconnected Lookup in Talend

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: How to implement Unconnected Lookup in Talend

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

Re: How to implement Unconnected Lookup in Talend

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.