Unconnected Lookups

[gunjos]

Hi,

I am a newbie to Talend/ java World. We are migrating from Informatica to Talend. We have a concept as "Unconnected Lookup" in Informatica, where you can refer lookup using function lookup(Key) and get corresponding value from the lookup. This function also can be used in if-else loop to connect multiple lookups or keys on the same lookup. 

I can achieve if-else functionality in tmap component when I have different Keys for the same lookup; however, I cannot implement if-else logic when I have different lookups, due to connected lookup. 

I can take a regular approach and put multiple splitters and tmaps to take care of each condition, but with 20-22 lookup, it's just very costly solution. 

Can anybody please suggest any routine or component where above logic can be handled? 

[cterenzi]

If you attach multiple lookups to your main data flow using a tMap, you can create a single output column with an expression like:
lookup1.columnName == null ? (lookup2.columnName == null ? (lookup3.columnName == null ? lookup4.columnName : lookup3.columnName) : lookup2.columnName) : lookup1.columnName

[gunjos]

Thanks for the suggestion. one question, if I attach all lookups in a single TMAP, won't it be heavy memory consuming job?

[cterenzi]

You know your data best. If it isn't feasible to pull all the lookup data together into one job, it may be possible to waterfall it so that records that successfully lookup a value in one lookup table are passed on and ones that don't match are rerouted to a different job.

We would need more specifics about your data sources to give specific design advice, but the broad answer is that you can implement if-then-else logic for your lookups, but you'll have to build it out yourself. The best way to build it depends heavily on your data sources. (i.e. If things are coming from common databases, pushing work onto the database may be possible. If things are coming from disparate systems or from files, a different approach would be needed.)

[gunjos]

Thanks.Can I implement this logic on joining keys? i.e. if key from main flow does not found in lookup 1 then search in lookup2 with Key2 and so on. 

[abhitalend · Accepted solution]

there is no concept of unconnected lookup in talend. You will have to understand what the functionality of an  unconnected lookup does and custom code it or use tmap.