finding matches between CSV and table

[DM_J]

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-25h	Jane	20030617	Jill	Smith
12c-25h	Do	20030617	Jill	Smith
12c-35er	Jill	19940405	Eve	Jackson
12c-25h	MaryAnn	20150812	Mary	Jackson
w2-e45ce	Jill	19990423	Eve	Randal

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.

[Anonymous]

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 🙂

[DM_J]

Hi Nikhil Thampi,

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