Skip to Content

Duplicates and Non-Duplicates Formula

Hi Experts,

I have below requirement

iam trying to pull duplicates records based on salary column create an flag variable as 1 if duplicate exist else 0 from the table. Could you please help.

Case 1: 1)Same Name Same Dept Same Salary

I tried using previous to retrieve the duplicates but the first record in any duplicate set has flag with empty.i expecting above all columns to include in the flag formula variable.

Flag_Case 1 = ?

Case 2: 2) same Name Same Dept but differnt saary

Flag_Case 2 = ?

Regards,

Dinya.

capture1.jpg (13.3 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Mar 22, 2017 at 11:12 AM

    check if this helps you.

    Create variable Case1=If([Name]=Previous([Name]) And ([Dept]=Previous([Dept])) And ([Sal]=Previous([Sal]))) Then 1 Else 0

    Place this in the Case1 column=Max([Case1]) In ([Name];[Dept];[Sal])

    Create variable Case2=If([Name]=Previous([Name]) And ([Dept]=Previous([Dept])) And ([Sal]<>Previous([Sal]))) Then 1 Else 0

    Place this in the case2 column=Max([Case2]) In ([Name];[Dept];[Sal])

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Amit,

      Could you help with non-duplicate case alone. here i would expect case 2 column value to display 1 for both records because it has:

      Case 2: same Name Same Dept but different salary

      Thanks,

      Dinya.

      capture1.jpg (15.3 kB)
  • Mar 22, 2017 at 09:18 PM

    Hi Amit,

    Thanks Much this worked great, i have one question in your formula, how to handle if there is any null value in salary measure for both duplicate and non-duplicate cases 1 &2.

    Regards,

    Dinya.

    Add comment
    10|10000 characters needed characters exceeded