topic Re: Conditional Lookup in Talend Studio

Conditional Lookup

Anonymous — Wed, 11 May 2016 13:13:34 GMT

Main Table
A1.City
A1.Country
A1.State

Lookup Table
A2.City
A2.Country
A2.State

Sometimes A1 will have just city and state and country is null.
If I join Expr.key like this
A1.City A2.City
A1.Country A2.Country
A1.State A2.State

In case A1.State is null if will not match because it performs the lookup anyways. Is there a way to not lookup a specific column based on a condition? I have tried (A1.State == Null)?"":A1.State. To no avail because now it is trying to lookup empty string.

Re: Conditional Lookup

Anonymous — Wed, 11 May 2016 14:30:32 GMT

I hope you use tmap, ist not clear from your question

in your tmap you need to adjust the jointype for your lookup (in case of this example row5 is the lookup):

HTH

Re: Conditional Lookup

Anonymous — Thu, 12 May 2016 07:13:11 GMT

Yes I am using tMap, sorry for not being clear.

Right now I am performing lookups of 3 columns

A1.City --> A2.City
A1.Country --> A2.Country
A1.State --> A2.State

What I want is in case A1.State is null, do not perform the lookup of that column

so it will be just
A1.City --> A2.City
A1.Country --> A2.Country
Is something like this possible. Or is there other recommended way of doing it?

Re: Conditional Lookup

Anonymous — Thu, 12 May 2016 07:37:42 GMT

As djdejay says, your requirement is not clear; but it sounds like you may want a left outer join.
You then join on all three keys. If a row is not found in table B, you only get the data from table A.

Re: Conditional Lookup

Anonymous — Thu, 12 May 2016 08:27:27 GMT

Lets try again, sorry I am pretty new at this.
I have 3 lookup column.

Sometimes, stete_id is null, so it wont be able to find the city at all.

In that case I want it to ignore the lookup of that column and continue to look for city. I have tried:

This doesn't work because instead of null now it is looking for white space.

This works:

but it will not take state into account at all.

All I want is when state Id is null...do not perform lookup of just state_id column.

Re: Conditional Lookup

Anonymous — Thu, 12 May 2016 08:30:38 GMT

I think you need show a clear example of your two input files and your expected output file, highlighting the rows that are an issue and their expected result.
It still sounds like your looking for an outer join

Re: Conditional Lookup

Anonymous — Thu, 12 May 2016 08:34:47 GMT

uploaded image

Re: Conditional Lookup

Anonymous — Thu, 12 May 2016 11:39:27 GMT

Try this:
(out2.state_id==null ? state_id : out2.state_id)
in this case the joincondition becomes state_id = state_id which is always true

Re: Conditional Lookup

Anonymous — Fri, 13 May 2016 11:47:17 GMT

I get state_id cannot be resolved to a variable.
I even tried
(out2.state_id==null ? row3.state_id : out2.state_id)
Ans I still get row3 cannot be resolved to a variable.

Re: Conditional Lookup

Anonymous — Fri, 13 May 2016 16:48:21 GMT

you should do the lookups in 2 or 3 steps (tmaps)
first match state, then City then ....
in this case your Need only 1 lookup condition and you can easily do left outer join

but i am still not sure what you wanna do