topic Re: How to return a value based on a lookup if value appears in a string in Talend Studio

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

JM39 — Sat, 16 Nov 2024 02:13:59 GMT

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

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!

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

Prakhar1 — Thu, 04 Jun 2020 22:27:57 GMT

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 ?

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

JM39 — Fri, 05 Jun 2020 08:05:06 GMT

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!

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

akumar2301 — Fri, 05 Jun 2020 11:07:59 GMT

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

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

JM39 — Fri, 05 Jun 2020 12:30:49 GMT

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?

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

akumar2301 — Fri, 05 Jun 2020 12:37:16 GMT

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.

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

JM39 — Fri, 05 Jun 2020 12:57:51 GMT

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

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

akumar2301 — Fri, 05 Jun 2020 13:08:01 GMT

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/126982

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

JM39 — Fri, 05 Jun 2020 15:18:02 GMT

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