Hi All,
After concat this is the column value I am getting.
1 | Apples, Oranges, Apples, Apples,Apples,Oranges |
2 | Kiwi,Banana,Banana,Banana,Kiwi |
How can I now show only unique values like grouping.
Expected output
1 Apples, Oranges
2 Kiwi, Banana
Thanks
Solved! Go to Solution.
Hi @JDong ,
you could split the values to rows using the Text to Columns tool, select unique values only using the Unique tool and "re-concatenate" using the Summarize tool.
I've attached a sample workflow.
Let me know if it works for you.
Best,
Roland
Thanks Roland.
The actual data has 40 columns and not sure if I need to select all the columns in the unique field ?
Also some rows where no values are available the data is in the below formats and want to clean up the delimiters
Apples,
,,,,,,,,
,Apples,,Oranges
,Oranges,,,,,Apples
I refer to this helpful post
And the below regex works perfectly fine
regex_replace([field],"\b(.+),(?=.*\b\1,?)","")
The only issue is some values have commas only like the below
,,,,,,,,,,,,,,,,,,,,
,,
,,,,,
Is there anyway I can remove the commas only in rows where there is no text like Apples, Oranges?
Thanks
Hi @JDong
Since you mention that you get the duplicates after concatenation, you should probably remove them before you concatenate. Your data before is probably this
Use a Unique tool to remove the duplicates before the summarize tool
Giving you
Dan
Thanks or something like this formula field also worked before the summarize tool
IF Trim([field])='' THEN Null()
ELSE [field] ENDIF