Forum Discussion

ConnieMaldonado's avatar
ConnieMaldonado
Icon for Responsive Resident rankResponsive Resident
3 years ago
Solved

Filter by Dups to Perform Analysis

I have a table that includes the following:

 

Last_RegisterEmailNoVersionStatusAccountLast_TripEECount EmailsRemove
11/15/2022 13:42[email protected]123456781not avail12345 1232FALSE
3/9/2023 7:09[email protected]123456782registered234563/9/2023 11:391232FALSE
2/11/2023 13:05[email protected]234567891not avail345672/11/2023 17:453453FALSE
3/9/2023 7:28[email protected]234567891registered456783/9/2023 11:593453FALSE
3/9/2023 7:30[email protected]345678902registered56789 3453FALSE
3/7/2023 16:40[email protected]456789011pending678903/7/2023 21:166782FALSE
3/9/2023 7:26[email protected]456789011registered78901 6782FALSE

 

I am trying to create a "Remove" filter to determine whether the record should be removed from the data.

 

So first I need to identify dup records based on email.  Then, based on certain criteria involving No, status and registered date, I need to build logic to determine which record(s) to remove.  I created a "Remove" column which is FALSE for now (until I build the logic).

 

I was able to identify records with duplicate emails by creating the following column:

 

Count Emails =
Var Emails = Table[Email]
RETURN

CALCULATE(
    COUNTROWS(Table),
    all(Table),
    Table[Email] = Emails
)
 
I have no idea where to begin to isolate a "set" of dups and determine which to remove.
 
For example, for a set of "dups", let's say [email protected], I want to keep the record with the latest "Last_Register" date and remove the others.  So I would set Remove = TRUE for the first record with Last_Register = 11/15/2022 13:42. 
 
Here's what the results would look like:
Last_RegisterEmailNoVersionStatusAccountLast_TripEmployee NoCount EmailsRemove
11/15/2022 13:42[email protected]123456781not avail12345 1232TRUE
3/9/2023 7:09[email protected]123456782registered234563/9/2023 11:391232FALSE
2/11/2023 13:05[email protected]234567891not avail345672/11/2023 17:453453TRUE
3/9/2023 7:28[email protected]234567891registered456783/9/2023 11:593453TRUE
3/9/2023 7:30[email protected]345678902registered56789 3453FALSE
3/7/2023 16:40[email protected]456789011pending678903/7/2023 21:166782TRUE
3/9/2023 7:26[email protected]456789011registered78901 6782FALSE
 
How would I build that logic - i.e., to set Remove = "TRUE" for record(s) with the earlier "Last_Register" date for dups based on email.  If I can build that logic, I can figure out the rest.  Just not sure where to start.
 
Any help would be appreciated.  Thank you.
Need Help

1 Reply