cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Check if all fields in a specific column are uniquie

Meteor

Hi,

 

I have a spreadsheet which has a column called "Reference".

 

I want to do a simple check that checks to make sure all references are unique e,g,

 

Reference
1

2

3

 

And to stop the workflow and display an error/message which says which reference is duplicated.

 

E.g. if the below was inputted:

 

Reference

2

1

2

3

 

it would stop the work flow and say

"Reference 1 is duplicated"

"Reference 2 is duplicated"

 

Alteryx Certified Partner
Use a unique tool on that field and then a test tool out of the bottom output (d for duplicates). Set the test tool to test if record count = 0.

Then on your workflow runtime check "Cancel Running Workflow on Error" - an error your test tool will produce if there are any duplicates coming out of the unique tool.

Edit: Inbetween the test tool and the unique tool, use the message tool to output a message list of each row coming through as duplicate.
Asteroid

EDIT - using Balder's solution I came up with the following that seems to do the trick, I couldn't get it to work the way it was described I could only get one message to show up for one duplicate not all of them but modifying my original approach to use the message tool and the test tool I get all the messages to come up and the workflow to stop on the error. The rest of the flow you would build off the Unique branch of the duplicate check.

 

I can't get you all the way there as I'm just a newbie myself, but you could put a Summary Tool in the flow and group by the Reference field, and count by the reference tool then feed the result into a test tool that looks at if the count of reference is less than 2 and if it isn't the test produces an error. If you save that as an application and run it you even get a popup telling you it's an error. In the interface tools there's probably a more elegant solution to this but at least this is a start for ideas. 

Meteor

Thanks for getting back to me. When i try and write a message expression in the message tool i enter the following 

 

"The reference:" + [Reference] + "is duplicated"

 

However it does not seem to pick up the [Reference].

 

Any ideas?

Asteroid

If you look in the workflow I provided you'll see an extra little bit of code in the expression required to make the [Reference] show up. Since in your example the [Reference] is a numeric value Alteryx reads it as a number and you can't add a number to text. If you add the TOSTRING([Reference]) to your expression you should find the reference gets pulled in because it forces the value in [Reference] into a string format and then it will add to your expression. You might also want to add a space to the end of your first text block and to the beginning of the second block so your reference doesn't mash into your text.

 

"The reference: " + TOSTRING([Reference]) + " is duplicated"

Asteroid

Let me know if my solution worked for you and if so don't forget to mark it as solved so others can benefit when they search for something along the same lines they'll be able to quickly go to the solution.

 

Thanks and happy data blending!