Concatenate Unique values (only)

[jakek]

@KoryKramer @DrewFlater  The implementation of the concatenate option has been great, thank you, but what I see now is the need for a "concatenate UNIQUE" option.  See this tech support  example: https://support.esri.com/en-us/knowledge-base/how-to-concatenate-field-values-using-a-case-field-in-....

It lists the same value multiple times (i.e. 111).  I would like to see the option to only show unique values (111 would only show once).  My use of this today resulted in the same value populated numerous times making the field width in excess of 500 characters.  Would it be possible to add a checkbox option to only return unique values?

Previous idea: https://community.esri.com/t5/arcgis-pro-ideas/arcgis-pro-2-6-a-geoprocessing-tool-to-concatenate/id...

[MarkBryant]

You can run a field calculator on the concatenated field.

def sortValues(the_value, delimiter=','):
    """Sort a separated value into an order
    Useful to run on ArcGIS processes where there is no control on returned values.
    For example SpatialJoin, JOIN_ONE_TO_ONE, with FieldMapping Merge Rule = Join
    """
    if the_value == None:
        return the_value

    # Create a Python list by splitting the string on the delimeter
    the_list = the_value.split(delimiter)
    # unique elements of the list using set()
    the_set = set(the_list)
    # turn the set back to a list
    the_list = list(the_set)
    # Sort the list
    the_list.sort()

    # Reassemble the string from the sorted list
    rval = delimiter.join(the_list)
    return rval

[wayfaringrob]

Is this regarding field mapping? If so, yes, concatenate unique would be very, very useful. I'm sure there's a use for it, but I really only need each unique value listed in my output once. I'm doing a spatial join if that makes sense.

[Bud]

For anyone who's using Oracle, this could be done using the LISTAGG SQL aggregate function. https://stackoverflow.com/questions/11510870/listagg-in-oracle-to-return-distinct-values. It would be done in a full SQL query, such as a query layer or a database view.

Other enterprise database types might support similar behaviour. Maybe mobile geodatabases (SQLite) too.

[RandyMcGregor_BMcD]

I agree. I use this to transfer field values over  from one layer (ie counties) to another (ie map extents) and if the same value appears twice, it is added both times. For features, you can try a 'dissolve' on the field you want to concatenate to remove redundant instances of this value before running the summary with concatenate option. It's an extra step, but if you model it up you can reduce to one step - DissolveSumConcatenate.

[David_Kimball]

Yes! Adding "Concatenate Unique" as a Summary Statistics "Statistic Type" option would be amazing. Concatenate is sort of useful but most of the time the results are unwieldy and require further processing to be useful.

I saw another post somewhere that said you could first create a summary table on the two fields and then do a summary table on the results to get a unique Concatenate field, but that's complicated and creates an additional unneeded table.

Also, that knowledge base article uses kind of a weird example - if you know that all of the values in the "case" field will have the same value in the other field, why wouldn't you just do a summary table on both fields? You'd get a cleaner output (without duplicate values in the concatenated field). The actual usefulness of Concatenate is to find all the distinct values that occur with each "case" field value and put them together. But the results of a "Concatenate Unique" statistic type would be so much more easy to deal with in most instances.