Duplicate Analysis

Question

Hi All,

I am trying to identify duplicate records even when some values are missing. For example,

IdFirstNameLastNameEmailPhone 1JaneDoeJane.Doe@gmail.com123-345-78912Jane Jane.Doe@gmail.com123-345-78913JaneDoe 123-345-78914Jane  123-345-78915JaneSmithJane.Smith@gmail.com567-111-1111

Records 1, 2, 3, and 4 are the same and should be flagged. However, when I use the Unique tool and select the four fields, each record is considered unique because the blank/null value is counted.

If I use the filter tool to exclude records that have blank values, then they are not evaluated for the analysis.

Let me know if you have any ideas. Thank you!!

ChrisTX · Answer

You may want to try a waterfall approach.... first try to match on name, then email, then phone, and/or a combination of fields.

The records that flow out of the first waterfall are those that did not match on the first criteria.

Here's an example workflow under the Help menu.  Each portion of the waterfall is when the workflow takes only the Left or Right output from a Join tool.

Chris

amartinezalq · Answer

Hi @ShankerV,

Thank you for your response.

I am applying it to a large dataset in which there is no column with a consistent value present. In other words, sometimes there is no phone. Additionally, there are other columns that I need to evaluate (address, company, etc.).

IdFirstNameLastNameEmailPhone MailingCity1JaneDoeJane.Doe@gmail.com123-345-7891Minneapolis2Jane Jane.Doe@gmail.com123-345-7891 3JaneDoe 123-345-7891 4Jane  123-345-7891 5JaneSmithJane.Smith@gmail.com567-111-1111 6  Jane.Doe@gmail.com Minneapolis

Records 1, 2, 3, 4, and 6 should be flagged.

Any ideas?

ShankerV · Answer

Hi @amartinezalq

One way of doing this.

IF [Phone ]=[Row+1:Phone ]
THEN "Flagged as duplicate"
ELSEIF [Phone ]=[Row-1:Phone ]
THEN "Flagged as duplicate"
ELSE ""
ENDIF

Many thanks

Shanker V