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!
