Announcement | We'll be doing maintenance between 2-3 hours, which may impact your experience. Thanks for your patience as we work on improving the community!

Tool Mastery

Explore a diverse compilation of articles that take an in-depth look at Designer tools.
Become a Tool Master

Learn how you can share your expertise with the Community

LEARN MORE

Tool Mastery | Unique

Ozzie
Alteryx
Alteryx
Created

Unique.pngThis article is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the Unique Tool on our way to mastering the Alteryx Designer:

 

Unlike a snowflake, it is actually possible for duplicates to exist when it comes to data. To distinguish whether or not a record in your data is unique or a duplicate we have an awesome tool called the UniqueTool that will actually turn your data into a unique snowflake.

 

The Unique Tool at its core is very simple to understand. In the configuration, you select for which columns you want the records to be uniquely distinguished. If only one field is selected it will only look for the instance of unique records in that particular field. If two or more columns are selected, the uniqueness of the data set will be distinguished by the combination of records. The U output will show a list of records that are unique depending upon the fields you selected, while the D output will give you the list of duplicates. It’s also helpful to know that the first record in each unique group is sent to the U output stream while the remaining records are sent to the Duplicate output stream.

 

unique.PNG

 

For example, let’s say you have a dataset with two fields: First Name and Last Name. And you have two records: where one is John Smith and the other is John Johnson. If you selected just First Name in the configuration, Depending on which record came first you would have that record come into that U output stream while the other would come out of the D stream. If you selected both first name and last name both John Smith and John Johnson would appear in the unique output stream.

 

A good sample to reference and learn from is the sample provided within Alteryx itself. Click on the Unique tool in the Preparation palette and click on “Open Example” to access this workflow.

 

Here are some common ways to use a Unique Tool:

 

  • Checking uniqueness before assigning primary keys to insert records into a database table with constraints

  • Obtaining the first instance of a unique record similar to a Summarize Tool "First" action. Because the order of the records matter, you can use this to your advantage to sort records in any way you desire before performing a unique to obtain the first record in any group of duplicates.

 

Now that you have seen what the Unique Tool is all about, you can finally journey on to discover all the duplicates hidden in your data. And remember:

 

6cec7c43ff0cf117f34df7e9f254ced5.jpg

 

By now, you should have expert-level proficiency with the Unique Tool! If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Let us know at Community@alteryx.com if you’d like your creative tool uses to be featured in the Tool Mastery Series.”

Stay tuned with our latest posts every Tool Tuesday by following Alteryx on Twitter! If you want to master all the Designer tools, consider subscribing for email notifications.

Additional Information
Click on the corresponding language link below to access this article in another language -
Portuguese
Spanish
French
German
Japanese

Comments
SeanAdams
17 - Castor
17 - Castor

I use the Unique tool in combination with a sort fairly frequently to find the original version or latest version of versioned data.   Note; You can also do this with a sort, followed by a Sample with a grouping.

 

For example - the following data

IDVersionDateValue
11-Jan-17Red
12-Jan-17Blue
13-Jan-17Green
23-Jan-17Red

 

If you wanted to find the original color for ID 1, just take the data, sort by ID and Date Ascending; and then unique on ID.

If you wanted to find the latest colour for ID 1, reverse the sort on the date field (descending) and then unique on ID.

 

Very quick way to trim through versioned data.

BobSnyder85
8 - Asteroid

Thanks for this write up!

 

I notice (and use) the fact the the Unique Tool uses the first instance of a record as the 'Unique' and then other instances as 'Duplicate'.

 

However, The records from the Unique output are not in the same sort order as inputted. I validated this by sorting, unique'ing :), then sorting again and comparing the outputs from the Unique to the Sorted Unique and they were very different. 

 

Please at least add the option to let the user decide how to sort the results in the future. 

 

 

TaraM
Alteryx Alumni (Retired)

Hi @BobSnyder85

The Unique tool is what we refer to as a blocking tool. It means records are sorted prior to executing the tool process. This is why the records do not come out in the order they came in. They come out sorted by the field or fields you selected to determine their uniqueness. You can easily sort back afterwards using a sort tool.

 

If you would like product management to consider a preserve sort order option in the tool, you can submit this suggestion on our Product Ideas board.

 

BobSnyder85
8 - Asteroid

Thanks @TaraM, I ended up posting that over there :)

serendipitytech
8 - Asteroid

So when using this tool, I'm trying to get a set of data that contains only unique records, if a record has a duplicate, I need it in another bucket to ponder later. 

 

I'm not sure what the best method for this will be, other than taking the D output, feeding it to a Filter tool, filter the original list so that all matches are in the TRUE and the truly unique records are in the FALSE. I'm sure there's a way to auto feed the D records to a filter, but I'm still rather inexperienced so I'd have to end up copying pasting the results to the filters and creating cascading filters. 

MaureenW
Alteryx Alumni (Retired)

@serendipitytech

I would recommend using the CREW macros developed by @AdamR_AYX.

These are extra macros built by Adam and some other contributors (Alteryx colleagues, partners, etc.) and there is one called Only Unique that does exactly what you are looking for.

https://community.alteryx.com/t5/Engine-Works-Blog/Crew-Macro-Pack-2016-Q2-Release/ba-p/26482

 

Nagesh_Bandaru
6 - Meteoroid

Hi serendipitytech,

 

We can accomplish this by following the below method.

We can use a combination of Tile, Sample, and, Filter Tool.

Below is the approach.

 

I took below data as an example: in this, I ensured duplicates are scattered around the data and at the same place.Data.PNG

 

Below is the complete workflow.

Results.PNG

 

Tile Tool - Helps give the sequence number generation based on the group, when the max number of any group is 1 means it is a truly unique value.

 

Sample Tool - We are picking up the last value of the group, so if the value is duplicated then it will have a number higher than 1, so we can catch there.

 

Filter Tool: splits truly unique and others

 

 
 

 

 

 

 

dianap
6 - Meteoroid

Hi, it would be really helpful if you add attachments with the tool mastery articles so we can practice as we read. Thanks!

LaurienM
6 - Meteoroid

Thank you for this helpful post!

 

One question.. I understand that the Unique Tool always takes the first observation/value of a set of duplicates, but is there any way to change this? If you believe that the first duplicates have something in common (for example: they are all the first observation in a times series dataset, and the dataset shows some seasonality which may influence the results) you might want to change the way in which the unique tool selects duplicates. I would prefer sth that includes more randomization. What would be possible to change this?

mjsnyder
7 - Meteor

This is a great tool to see if the data is unique or contains duplicates in the data set.

Tyrane782001
7 - Meteor

yes... it's a good tool in seeing duplicate but how do you sort with different sensitive character 

mvtejano
7 - Meteor

This is interesting and very helpful. Thank you.

Anitha1289
7 - Meteor

Hi, it would be really helpful if you add attachments with the tool mastery articles so we can practice as we read. Thanks!

0
 
 
 
 
nadiralam
7 - Meteor

This is a great tool to see if the data is unique or contains duplicates in the data set.

aryan_kushwaha
5 - Atom

Very Helpful Tool