AnsweredAssumed Answered

Seperate Duplicates and Merge Duplicates

Question asked by robin.zhang344808 on Sep 24, 2018
Latest reply on Sep 24, 2018 by trey.yeager826393

Hi, 

I am new to boomi and try to implement a complex business logic to remove duplicate and to merge duplicate with empty fields. Any help will be much appreciated (ideal about how process looks like or how to start on a groovy script! 

 

Goal:

1) To separate duplicate from unique records

2) Merge duplicates

 

File format: Flat files (CSV)

 

Sample files: 

First name

Last name

Email

Postal code

Phone number

ID

John

Smith

123@gmail.com

 

 

1

John

Smith

123@gmail.com

234

 

2

John

Smith

123@gmail.com

234

21432423

3

John

Smith

TBA

456

 

4

John

Smith

 

678

 

5

Robin

Zhang

234@gmail.com

765

34543534

6

 

Business logic:

  1.  1.Find duplicates (first name + last name + email) - concatenate three fields
    1.    a   email is either empty or 'TBA', treat as a unique record
  2.     Merge duplicates fields
  1.     Expected results for duplicate lookup - separate unique records from duplicate records, but still keeping duplicates

Unique table

First name

Last name

Email

Postal code

Phone number

ID

John

Smith

123@gmail.com

 

 

1

John

Smith

TBA

456

 

4

John

Smith

 

678

 

5

Robin

Zhang

234@gmail.com

765

34543534

6

 

Duplicate table

First name

Last name

Email

Postal code

Phone number

ID

John

Smith

123@gmail.com

234

 

2

John

Smith

123@gmail.com

234

21432423

3

 

  1.      Expected results for duplicate merge - update John smith in unique table from duplicate table for the empty fields. For example, if both john smith in duplicate table has phone number, use the one with highest client ID to update John smith in unique table. (migration dedup) 

 

Unique table

First name

Last name

Email

Postal code

Phone number

ID

John

Smith

123@gmail.com

234

21432423

1

John

Smith

TBA

456

 

4

John

Smith

 

678

 

5

Robin

Zhang

234@gmail.com

765

34543534

6

 

Duplicate table

First name

Last name

Email

Postal code

Phone number

ID

John

Smith

123@gmail.com

234

 

2

John

Smith

123@gmail.com

234

21432423

3

 

Outcomes