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

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.

Omit old fees.xlsx

Workflow

Datasets

Reporting

Data Investigation

Accepted answers

Luke_C

Hi @MATTHEW_KING1

A sort and sample should work:

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

New Workflow5.yxmd

Luke_C

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')

All comments

Luke_C

Hi @MATTHEW_KING1

A sort and sample should work:

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

New Workflow5.yxmd

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

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703