Regex for unique values concatenation

[BB]

I am looking for a concatenation regex to concatenate unique values for a field. The values have to be grouped together for a ID. I have multiple fields that has to perform this. The null values must be ignored. 

The input looks like this -

ID                Date                 Code           Description

111              01/01/2020       123             This is a good code. Use this code.

111              01/12/2020        0000          Special code.

111              01/01/2020        123            This is a good code. Use this code.

111              01/01/2020         456           Incorrect code.

222              03/01/2020        234            Nice code.

222              04/01/2020        234            Nice code.

The output should look like this with unique values concatenated -

ID      Date                                   Code                    Description

111    01/01/2020,01/12/2020     123,0000,456    This is a good code. Use this code.,Special code.,Incorrect code.

222   03/01/2020,04/01/2020      234                    Nice code.

I am using Summarize tool for Group By on ID and Concat on Date, Code and Description. After the Summarize, I am using Multi Field Formula tool to get unique values for Date, Code and Description. However the regex below is not working for Date and Description. I am not that good that Regex, I really need help with this.

REPLACE(TRIM(REGEX_Replace([_CurrentField_], "\b(.+),(?=.*\b\1,?)", ""))," ",", ")

Thanks much!

[BrandonB]

How about this? Workflow is attached!

 

[mceleavey]

Hi @BB ,

This should work:

[BB]

Thanks Brandon for the quick response. Yes, this works if I have a handful of fields. My data has 64 fields of which about 70% of them would need to go through a Group By and Concat as they could have multiple values returned from the database. I was looking for a regex that would help me get away from workflow clutter.

[mceleavey]

Hi @BB ,

I see the issue.

Try the attached workflow, that should be dynamic.

M.

[BrandonB · Accepted solution]

I think that @mceleavey solution is perfect but I would throw a unique tool in there to get rid of the duplicates. 

 

[mceleavey · Accepted solution]

Hi @BrandonB ,

Yeah I caught that after I posted it. I fixed it in the second solution.

M.

[BB]

Thanks so much