question for correct Alteryxx tool for multi-row action

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

Workflow

Accepted answers

surfside1

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-a-date/td-p/6455. 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!

All comments

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703