question for correct Alteryxx tool for multi-row action

[surfside1]

Alteryx Version: 2018.4

I recently sent a post and immediately receive an authentication error. I rewrote the post and am curious if it actually posted

My profile is surfside1

I am still getting an error trying to repost the following:

it keeps telling me to: Correct the highlighted errors and try again.

• There was an error while attempting to post your message. Try again in a few minutes.

I'm questioning if the Multi-Row Formula Tool is the tool to use. I have a table for Exceptions. Each row has a unique Exception ID unless it has an Extension. if it has an Extension, at the end of the row it will have an "Extension ID", "EXT New Expiration Date" and other info. So if an Exception has multiple Extensions the Exception ID will be duplicated in the table. What I would like to do is to delete the duplicate Exception ID rows with the older "EXT New Expiration Date" and keep only the newest Expiration Date row.   is this where the Multi-Row Formula Tool can be used? If so I could use some help with the Row syntax. I have all the rows sorted by "Exception Id" (ASC) and "EXT New Expiration Date" (DEC). I am assuming I would continue to loop through the rows to see if the Exception ID matches the next row and (if date is newer then the next) then delete the next row (row+1)?.

I've started out with something like this:

Create a new field "Remove Extension"

IF([Exception ID] (assuming this is current row?) = ([Row+1:Exception ID])

THEN "Delete" (Row+1:[Remove Extension] (meaning put the value of "Delete" into a new field called "Remove Extension" in the next row)

ENDIF

(I would then check in another tool if that field "Remove Extension" is "Delete", I'd delete those records?)

As you can see I need help!

I've done this in multiple Access Queries and temp tables but thought it might be handled in this tool?

This replaces a temporary post I posted due to having messages delete due to authentication issues.

Thanks,

Surfside1

[scottj]

You could sort by Extension ID (ascending) and also Date (descending).  This would put your newest ID at the top.  Then use a Unique tool, with Extension ID selected.  This will keep the newest, as the Unique tool takes the 1st instance.

[surfside1]

Thanks Scott, sorry I didn't respond, I didn't receive an email and I didn't look far enough down to see your response!

I've tried what you suggested and it was hit or miss that it selected the one with the "new expiration date" so I tried sorting the date ascending and different results but still hit or miss on the correct one.

Question, can you place criteria in the Unique tool to specify that we want the most recent "new expiration date"?

Thanks!

[scottj]

No, the Unique Tool will just take the 1st instance of whatever fields are uniqued, so sort is important.  Another option may be the Sample Tool.  With this tool you can set it to take the 1st N records (N being 1 in your case) and then choose fields to Group.  So, if you wanted the 1st date, only once, then group on date and take the 1st.  If you want the 1st date for a specific person let's say, then sort on the person and date (descending to get the latest), Then group by Person, and take the first 1.

[surfside1 · Accepted solution]

Ok, I think I am going to try to stick to your unique tool suggestion.  I'm certain my issue is that my date field was not defined as Date in the select tool and now I am realizing some of the dates are blank and am reading something about that which can cause issues also at https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-can-I-convert-a-field-of-nulls-to-... But unfortunately I need to run and pick this back up in the morning.  Unfortunately I have to do my real work during the day and this is for future development that always takes a back burner :(

I'll be back!

Thanks so much!

[surfside1]

Thanks this was the correct solution for my problem!  I just had to make sure my date was formatted correctly before the sort to yyyy-mm-dd.

Sorry it took so long to reply.  Appreciate the help!