Forum Discussion

aewing13's avatar
aewing13
Frequent Visitor
2 years ago
Solved

Multiple Duplicate Tests

Hi all,

 

I'm curious if there are any ideal methods for performing multiple duplicate tests on the same table, each testing different columns.

 

For example, from the below, I'd like to identify duplicates on the following columns 1) ID and Invoice, 2) Date and Amount. Ideally, I'd like a flag field like I've shown below in the DuplicatesFlag1 and DuplicatesFlag2 fields, but I am not sure if that is an option.

 

My current thought is that I'll have to create separate copies of the table and use the group by functionality for each duplicate test, and then join back to the main table to create the flags.

 

I appreciate any and all advice! Thank you.

 

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    Write these calculated column formulas

    Column = 1*(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Invoice]=EARLIER(Data[Invoice])))>1)
    Column 2 = 1*(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Date]=EARLIER(Data[Date])&&Data[Amount]=EARLIER(Data[Amount])))>1)

    Hope this helps.

     

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Write these calculated column formulas

    Column = 1*(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Invoice]=EARLIER(Data[Invoice])))>1)
    Column 2 = 1*(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Date]=EARLIER(Data[Date])&&Data[Amount]=EARLIER(Data[Amount])))>1)

    Hope this helps.