How to return a value based on a lookup if value appears in a string

[JM39]

Hello.

I'm not sure if what I'm trying to achieve is possible as I'm very new to Talend.  I have a list of addresses and I want to derive the country code from those addresses, but the country values are not consistently in the same field in the source data.  So for example it could be

Addr1

Addr2

town

country

OR

Addr1

Addr2

Country

Town

So I want to be able to concatenate all address fields into one (removing commas) and separate them with a space like this

Addr1 Addr2 United Kingdom London

Then I want to use this concatenated field with a lookup and if United Kingdom is found anywhere in that string, return the country code GB from the lookup

Is that possible?  Thanks!

[Prakhar1]

I think that can be achieved , but some questions like :

1) why you need to concatenate it ? when you are searching for country value in the string, you can directly use the country column for that .

2) can you tell the structure of you look table and how the data will look there ?

[JM39]

Thanks.  

1) I need to concatenate it because the country doesn't always appear in the same column in the address.  So if I have a total of five address fields, sometimes it's in addr line2, sometimes it's in the town, sometimes both the town and country are in the same field, and so on  

2) My lookup table will look like:

Country                    Code

United Kingdom        GB

United States             US

France                       FR

So if the word 'France' is contained in the concatenated string anywhere, I would want it to return the value of FR.

Thanks!

[akumar2301]

Hello do you have data Quality components. This is little tricky but give you idea of another solution.

https://help.talend.com/reader/hCrOzogIwKfuR3mPf~LydA/NBpEIgXnO6H0r2iUDTllgw

[JM39]

Thank you for that - If I'm reading it right, I would need to repeat that process for every address format for every country?  I think it's beyond my capabilities right now - I'm really a beginner with Talend.  I'm not too bothered about getting the rest of the address format right, I just need to identify the countries.  Is that possible?

[akumar2301]

Yes , I guess you have all data in sale field or do you have 4 records for each address ? Share some sample dataset.

It is possible what you are looking for.

[JM39]

so my data might look like what's in the attached.  As you can see the data is all mixed up in the columns.  Ideally I want to concatenate all that data into one column, then use a lookup to see if there's a country name contained in that string so that I can return the country code.

address sample.xlsx

[akumar2301]

to concatinate 

use tmap

col1 + " " + col2 + " " + col3 

now to replace the from lookup country :

https://community.talend.com/t5/Design-and-Development/Search-and-Replace-a-List-of-Substrings/td-p/...

[JM39]

I think I understand the concept of that, but can't get it to work in practice unfortunately.  Thank you anyway.