finding matches between CSV and table

Highlighted
Six Stars

finding matches between CSV and table

There is a CSV file with the data of people who changed there name: id, former_name, number, first_name, last_name

The former_name can be first or last name.

Here is a sample of data:

id former_name number first_name last_name

12c-25hJane20030617JillSmith
12c-25hDo20030617JillSmith
12c-35erJill19940405EveJackson
12c-25hMaryAnn20150812MaryJackson
w2-e45ceJill19990423EveRandal

Which Jane Do changed her first name first then changed her last name.

 

There is  a table: 

Person: person_id, first_name, last_name, number, ...

person_id is created  by first_name + last_name +number

 

I want to check if the person in the CSV file exists in the table or not.

As the id is the combination of the person's first and last name,

  1. Condition 1: I want to check if the old name and number exist in the id or not: id.contains(old_name) && id.contains(number)
  2. Condition2: if the result of 1 is true I want to check two combinations: person.firstname+csv.oldname+csv.number with id and csv.oldname+pesron.lastname+csv.number

If the result of condition 1 is false or the result of condition 2 is more than one record or any record I assume the person in the csv is a new person. So I will add her to the table

But if the result of condition 2 is only one record, I will update the row in the Person table.

 

I hope it is clear now.

 

Appreciate any help.

Employee

Re: finding matches between CSV and table

Hi,

 

     Unfortunately I am confused by your requirement :-( You had specified that there is a Person table and row1 is from Person table and row2 is from csv after Talend processing. Why are you joining both rows?

 

      Could you please repharse your query with example for both datasets and the expected output datasets for each of your query? Then we will get more idea about your requirement.

 

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: finding matches between CSV and table

Hi Nikhil Thampi,

 

I updated the question, I hope it is clear now.

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 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 3

Read about some useful Context Variable ideas

Blog