Need to omit rows, leaving the latest dates ones. Tried summarize, sample etc

[MATTHEW_KING1]

Hi, 

I hope you can help me please. I have a data set with many columns relating to sales and fee data. There are fees with IDs that are related to a Sales agreement number, and i am trying to omit those that have been superseded, but only if they have. I hope that makes sense. Example data set attached. Real data contains many more columns and rows.

[Luke_C · Accepted solution]

Hi @MATTHEW_KING1 

A sort and sample should work:

1. Sort data by Customer/Fee Description/Updated Date
2. Sample the first record for each group. (last updated)

[MATTHEW_KING1]

Thanks, very simple in the end. Overthought this one i think. Thanks Luke

[Luke_C]

Happy to help @MATTHEW_KING1! Please remember to mark the solution as accepted

[MATTHEW_KING1]

Thanks, done.

[MATTHEW_KING1]

Hi, quick question please. The Sort tool, it seems to only sort correctly if the date field is is a V String. If it is a Date field i am having issues having the sort tool bring the latest data on top. Would that be a correct assessment?

[MATTHEW_KING1]

Unsure its the sort tool. I switch the Last update descending part of your workflow in the sort tool to Fee End Date descending and it does not bring back the latest end dated fees. Do you know why would that be please? Driving me mad 🙂

[Luke_C · Accepted solution]

Hi @MATTHEW_KING1, fee end date is not being read as a date by alteryx, so the sorting won't work as you would expect.  You will have to convert that field to a date (yyyy-mm-dd) using either a datetime tool or a formula tool with the below:

Datetimeparse([Fee End Date],'%m/%d/%Y')

[MATTHEW_KING1]

Thanks Luke. Was having issues when changing a the data type from date in a select tool, the sorting worked, but then when changing it back to date it didnt even read it as it did originally, so i think the data type loses something when switching in a select tool and it has to be converted using one of your two methods above to bring it back to an actual date field.

[Luke_C]

Yup that's correct, updating the data type to date in a select tool will only work if the string is already in the accepted date format