Find a string in another table

Six Stars

Find a string in another table

In the middle of a flow, I need to find a string in another table, the two tables don't have any relations.

 

here is a sample:

Table CustomerInfo  100 000 rows

fields:

cust_id

cust_info

 

cust_info includes customer information like address, phone number, city, province separated by delimiters

I have a variable called prov extracted from above with the value of the province.

 

Table province  200 rows

id

name

code

country

 

If the prov's value exists in the province table(province name) I want to get the province code and country.

 

I tried to use this solution

https://community.talend.com/t5/Design-and-Development/Finding-keywords-in-a-tabel/m-p/151845#M92805

 

I used this code in a tMap base on the solution above, but the result is a cartesian as there are not any joins between tables.

code:

row21.name.contains(row6.prov)?row21.code:""

 

row21 is lookup from Table province and row 6 is from the main flow which I process Table CustomerInfo 

 

 

11.png

 

I think I should use something like tJavaFlex and load the Table province in the starting part and in the middle for every row of CustomerInfo table is province_name exists or not. I am not sure and I don't know how.

 

Appreciate any help.

Employee

Re: Find a string in another table

Hi,

 

    If you are having exact match for province data with lookup table, then you can avoid Cartesian match. You need to add the row6.prov as matching key for lookup table data.

 

    But if your lookup data is not in a standardized format, then you are in trouble. You can do mix and match by using contain clause to try to do matching. But I am not a fan of this as it may lead to data issues later. My suggestion would be always to standardize the entire address data as the first stage and use the clean data for matching in later stages.

 

    Talend has already got lot of Address standardization components as shown below.

image.png

 

Its up to individual customer to select which service they would prefer to use. I have used Experian data before for one of the customers for a big UK customer and the output of standardized data was in excellent condition to use further processing.

 

If you are planning for simple contains clause, there might be a chance of wrong match which will lead to data privacy related issues in the era of GDPR (which may result in unwanted legal issues). So discuss about all these aspects with your architect team before deciding on the match clause on raw data.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 

 

Six Stars

Re: Find a string in another table

the row6.prov can be empty or has a province which is not in the look-up table.

 

So I used the join model: left join and match model: unique, but it does not return the data. I all so did trim for both sides.

But it does not return any data for exact matches.

 

That is why I didn't use the matching. 

 

 

Employee

Re: Find a string in another table

Hi,

 

    Did you check whether the data is in same case for both main and lookup flows? It could be due to difference in case of the input data. Could you please do upper or lower case conversion and then try it? If you are still facing the problem, please share some 5 or 6 input records for both main and lookup flow for inspection.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download