Unique Tool - Option to Select Which Value to Keep Based on Formula/Other

[richarty]

Hello all,

Within the databases that I work in, I often find that there is duplicated data for some columns, and when using a unique tool, I have little control of what is deemed the unique record and which is deemed the duplicate.

A fantastic addition would be the ability to select which record you'd like to keep based on the type + a conditional. For example, if I had:

Field 1	Field 2	Field 3
1	a	NULL
2	a	15

I would want to keep the non-null field (or non-zero if I cleansed it). It'd be something like "Select record where [Field 1] is greatest and [Field 2] is not null" (which just sounds like a summarize tool + filter, but I think you can see the wider application of this)

I know that you can either change the sort order beforehand, use a summarize tool, or go Unique > Filter duplicates > Join > Select records -- but I want the ability to just have a conditional selection based on a variety of criteria as opposed to adding extra tools.

Anyways, that's just an idea! Thanks for considering its application!

Best,

Tyler

[SeanAdams]

Hey @richarty

This is a fairly common need and relatively easily solved:

The unique tool works in a deterministic way - it always keeps the first row it sees (in the order that they are sent) for each combination of keys specified.

So - if I have a person ID with person details which is versioned, I also have a version ID - then by sorting the incoming stream by personID ascending; and version ID descending -then I know that the first time the unique tool sees person ID X it will be the latest version.

Now - you may not have something as simple as version ID to sort on (which is an easy increasing number) but you can use a formula tool to create a sort column of any degree of complexity.

Happy to help with a mockup (just can't add mockups to idea postings) if you need a solution to a pressing problem - just drop your example with some sample input data into the Data Blending discussion board, and we can crack through this with you as a community.

Cheers

Sean

[Community_Admin]

The status of this idea has been changed to 'Inactive'. This status indicates that:

1. The idea has not had activity in the form of likes or comments in over a year.

2. The idea has not reached ten likes.

3. The idea is still in the 'New Idea' status. 

However, this doesn't mean your idea won't be implemented! The Community can still like and comment on this idea. With enough renewed interest, this idea can be brought back into the 'New Idea' status. 

Thank you for contributing to the Alteryx Community and the Alteryx Product Idea Boards!