Checking for duplicates

Question

Hi Community,

Suppose I have the following sample data:

StateCodeAlabamaALAlaskaAKArizonaALArkansasARAmerican SamoaASCaliforniaCAColoradoCOConnecticutCADelawareDEDistrict of ColumbiaCA

I want to ensure each State has its own unique Code. I would like to show an error message if this isn't the case. Using the above data, two error message would be shown like:

Error: the following states can't have the same code: Alabama, Arizona

Error: the following states can't have the same code: California, Connecticut, District of Columbia

My thinking is to create a table with the codes that have duplicates, and a second column with the corresponding states. For example:

CodeStates_ConcatALAlabama, ArizonaCACalifornia, Connecticut, District of Columbia

And then use the Message tool on above table. Any idea how to create above table? Also happy to see different approaches of course.

Thanks.

abacon · Accepted Answer

@RVDL Here is a workflow that does what you are looking for. It checks if there are duplicates then formats the table in the way you are talking and includes the message tool to show the error message.

Bacon

aatalai · Accepted Answer

@RVDL slightly different way to @abacon , count how many states that there are to each distinct code, then filter for those that aren't equal to 1, then join those to the original data set, then generate the error message using the summaries tool and conactating and grouping and use the message tool

flying008 · Accepted Answer

Hi, @RVDL