Handling duplicates based on statuses from one column (or multiple)

[trojan6ixman]

Hi all,

Recently, I've learned to account for duplicates and picking most recent dates, etc especially by this thread: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Pick-the-record-with-most-recent-date/...

However, now I am trying to account for duplicates but it cannot be solved simply by sorting on a date field. There is one column (or combinations of multiple) as seen in the attached image that I need to account for duplicates through. For example, I'd like to only keep the account numbers that are Remote and past the interview stage (so Interview and Offer Accepted). Another example would be only keeping the row that has the "Phone" type. There are many different ways to combine the statuses but that is the main point. 

How do I account for duplicates by categorical statuses? On top of that, if there are certain hierarchies in those statuses like New-->Interview-->Offer-->Offer Accepted how can I establish logic in a way that I can easily filter on them like between Offer/Interview stage, Before Offer stage, etc.? Any advice would help thank greatly!

 

[RobertOdera]

Hi, @trojan6ixman 

I would be happy to help if you can provide a sample with all the possible statuses and stage hierarchies as one moves from new to offer accepted - Cheers

[StellaBon]

Try first using the Summarize tool to group by Account Number, Type, and Status, then a Filter tool/s to filter for the specific statuses you want to keep. Or Filter Tool to keep [Phone Number] that Is Not Null.  There is also the Unique tool that will also help.

[Yoshiro_Fujimori]

@trojan6ixman ,

If the Status changes in a specific time order, it may be convenient to add a number to it as Status ID in the order.

You can use the Status ID in the Filter condtion like below.

After you filter to get all the rows which satisfies your conditions,

you can union them and de-duplicate by Summary Tool, Grouping by Account numer, and get the Max Status ID.

Does it work for your case?