5 Replies Latest reply on Oct 28, 2018 11:48 AM by Daniel Quek

# Checking Claim Duplication with +/- \$1 difference

Hi, I have the list of claims below which I would like to pick only those records with the same Name/Start Date/End Date and claim amounts that are same or with \$1 difference.

Is there a way to do it in Tableau Prep or Tableau itself?

 Name Start Date End Date Claim Amount Albert 29/10/18 3/11/18 \$2 Albert 29/10/18 3/11/18 \$3 Ben 31/10/18 3/11/18 \$3 Cherry 28/10/18 5/11/18 \$2 Daniel 29/10/18 4/11/18 \$1 Daniel 29/10/18 4/11/18 \$5 Tom 30/10/18 4/11/18 \$2 Tom 30/10/18 4/11/18 \$2
• ###### 1. Re: Checking Claim Duplication with +/- \$1 difference

Daniel,

Create a T|F calculated field as :

{fixed [Name],[Start Date],[End Date] : sum([Number of Records])}=2

and {fixed [Name],[Start Date],[End Date] : MAX([Claim Amount])-MIN([Claim Amount])}<=1

Put it on filter shelf and choose TRUE.

Thanks,

AB

• ###### 2. Re: Checking Claim Duplication with +/- \$1 difference

This should do what you're after:

• ###### 3. Re: Checking Claim Duplication with +/- \$1 difference

Hi Chris, thanks for the quick reply.

The solution for Tableau Prep is incorrect as there is no duplication for Cherry and Ben as they only submitted one claim each. Is there another route to go for Tableau Prep?

Furthermore, if I have one more record, Daniel - \$6, then I won't be able to identify the duplication for Daniel using the Min-Max method as the Min claim is \$1 and Max claim is \$6 for Daniel.

 Name Start Date End Date Claim Amount Albert 29/10/18 3/11/18 \$2 Albert 29/10/18 3/11/18 \$3 Ben 31/10/18 3/11/18 \$3 Cherry 28/10/18 5/11/18 \$2 Daniel 29/10/18 4/11/18 \$1 Daniel 29/10/18 4/11/18 \$5 Daniel 29/10/18 4/11/18 \$6 Tom 30/10/18 4/11/18 \$2 Tom 30/10/18 4/11/18 \$2
• ###### 4. Re: Checking Claim Duplication with +/- \$1 difference

You're right, try this:

• ###### 5. Re: Checking Claim Duplication with +/- \$1 difference

Hi Chris,

The final results that I intend to show should include the claim amount. Below is the final results that I hope to achieve. Anyway to do it using tableau or tableau prep?

Before

 Name Start Date End Date Claim Amount Albert 29/10/18 3/11/18 \$2 Albert 29/10/18 3/11/18 \$3 Ben 31/10/18 3/11/18 \$3 Cherry 28/10/18 5/11/18 \$2 Daniel 29/10/18 4/11/18 \$1 Daniel 29/10/18 4/11/18 \$5 Daniel 29/10/18 4/11/18 \$6 Tom 30/10/18 4/11/18 \$2 Tom 30/10/18 4/11/18 \$2

After duplication check (showing only the duplication records)

 Name Start Date End Date Claim Amount Albert 29/10/18 3/11/18 \$2 Albert 29/10/18 3/11/18 \$3 Daniel 29/10/18 4/11/18 \$5 Daniel 29/10/18 4/11/18 \$6 Tom 30/10/18 4/11/18 \$2 Tom 30/10/18 4/11/18 \$2