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!
Solved! Go to Solution.
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.
I think that @mceleavey solution is perfect but I would throw a unique tool in there to get rid of the duplicates.
Thanks so much