Duplicates and Non-Duplicates Formula

[former_member402770]

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.

[amitrathi239 · Accepted solution]

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])

[former_member402770 · Accepted solution]

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.

[former_member402770]

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.

[amitrathi239]

before doing any calculation add 0 in the measure object like Var1=Sal+0

or other you can try to sort the objects first to get the sequence.