Skip to Content
0

Duplicates and Non-Duplicates Formula

Mar 22, 2017 at 08:25 AM

58

avatar image

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)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
AMIT KUMAR
Mar 22, 2017 at 11:12 AM
0

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


untitled.png (14.7 kB)
Show 1 Share
10 |10000 characters needed characters left 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)
0
Din 44 Mar 22, 2017 at 09:18 PM
0

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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.

0