Conditional Lookup

[Anonymous]

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.

[Anonymous]

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



HTH

[Anonymous]

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?

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

uploaded image

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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