Compare equal fields & other fields to filter spreadsheet

Hello! I'm new to Alteryx, so apologies if this is a repeat/dumb question!

I have an Excel spreadsheet that contains fields that include date, names, and statuses. The spreadsheet is pretty messy. It has multiple entries that are identical, but it also has nearly identical entries where a date field or a status could be different. What I'm trying to do is filter out those that are either identical dupes (keeping just one) AND filter out those that are slightly different and only keep the relevant one (be it on status and//or date, since both could be the driver of the difference, or together they both identify which record to keep).

For example, one person could have two entries with the same name, date, but one entry could be "pending" and the other "approved." I'd like to be able to filter out "pending" since I'd want to keep approved when the information is the same.

Is this type of filtering possible to do in Alteryx? I've mainly been trying in Excel, but it is super time consuming for a spreadsheet we receive and need to do this for every day, so I was hoping this could be done in Alteryx and save the expression for continual use

Thanks!

Common Use Cases

Accepted answers

Joe_Mako

One option is to use the Formula too to add a field, lets call it "Priority", with the expression:

Switch([Status],0,
"Pending",1,
"In Progress",2,
"Approved",3)

This will assign a number to each "Status" value (larger values are higher priority and will be kept before lower priority values), then a Sort tool with Priority sorted Descending, and a Sample tool to select the first record per Date-Name combination Group.

Priority.yxmd

All comments

Joe_Mako

One option is to use the Formula too to add a field, lets call it "Priority", with the expression:

Switch([Status],0,
"Pending",1,
"In Progress",2,
"Approved",3)

Priority.yxmd

MKM

Thanks Joe_Mako! I'm pretty sure this is working how I wanted, though I'm not sure I'm doing the last part with the "Sample" tool right. I selected the "First N Records" when N =1, but I'm still not sure which combo of fields to pick in the "Sample" section. If I have records, based on ID number that appear multiple times and I want to pick to only keep the record with the most recent date & (thanks to this new field) the higher priority number, which fields would I select in the "Sample" section?

So far, my selecting options either removes almost every row or seemingly keeps nearly every row, so I know I'm doing something wrong still.

Thank you for the help!!!

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335