Concat distinct only

[Lobo77]

Hi all.

I am looking to concat the Status of distinct orders for a customer. Looking to do this front end , not script.

Data has a 'customer' level  – that can have multiple 'Orders' – and the Order can have  multiple 'Order Actions'.  However , each order action will have the same status.

Currently what I have is concating the statuses for the order actions  , when using aggr(concat(Status],'; ' ),[Customer]) . I know I am missing something very simple , and have tried many iterations   but have not been successful.

How would I do this for distinct order only   at a  customer level  ?

See below for current and expected result for 3 customers.

Any suggestions greatly appreciated.

Thanks.

Customer	Order	Order Action	STATUS	Using Currently    aggr(concat(Status],'; ' ),[Customer])	Expected
186641	67887642	7887643A	Closed	Closed; Closed; Closed; Closed	Closed
186641	67887642	7887644A	Closed	Closed; Closed; Closed; Closed	Closed
186641	67887642	7887645A	Closed	Closed; Closed; Closed; Closed	Closed
186641	67887642	7888403A	Closed	Closed; Closed; Closed; Closed	Closed
19808	65189197	5189198A	Cancelled	Cancelled; Cancelled; Closed	Cancelled; Cancelled; Closed
19808	67661240	7661595B	Cancelled	Cancelled; Cancelled; Closed	Cancelled; Cancelled; Closed
19808	67667479	7668574A	Closed	Cancelled; Cancelled; Closed	Cancelled; Cancelled; Closed
3438378	68866961	68866962A	Cancelled	Cancelled; Submitted; Submitted; Submitted; Submitted; Submitted; Submitted; Submitted	Cancelled; Submitted
3438378	68869074	68869075A	Submitted	Cancelled; Submitted; Submitted; Submitted; Submitted; Submitted; Submitted; Submitted	Cancelled; Submitted
3438378	68869074	68869080A	Submitted	Cancelled; Submitted; Submitted; Submitted; Submitted; Submitted; Submitted; Submitted	Cancelled; Submitted
3438378	68869074	68869081A	Submitted	Cancelled; Submitted; Submitted; Submitted; Submitted; Submitted; Submitted; Submitted	Cancelled; Submitted
3438378	68869074	68869082A	Submitted	Cancelled; Submitted; Submitted; Submitted; Submitted; Submitted; Submitted; Submitted	Cancelled; Submitted
3438378	68869074	68869925A	Submitted	Cancelled; Submitted; Submitted; Submitted; Submitted; Submitted; Submitted; Submitted	Cancelled; Submitted
3438378	68869074	68870589A	Submitted	Cancelled; Submitted; Submitted; Submitted; Submitted; Submitted; Submitted; Submitted	Cancelled; Submitted
3438378	68869074	68870597A	Submitted	Cancelled; Submitted; Submitted; Submitted; Submitted; Submitted; Submitted; Submitted	Cancelled; Submitted

[Lobo77 · Accepted solution]

Hi @ArnadoSandoval  , what ended up working was the below. It is not pretty , but it gives the result required. Thanks again for the assistance.

keepchar(aggr(concat(distinct [Order]&[Status],'; ' ),[Customer]),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz; ')

Edit - added ';' to keepchar

[Kushal_Chawda]

@Lobo77  try below

=concat(aggr(Concat(DISTINCT Status),Customer,Order),';')

[Lobo77]

@Kushal_Chawda  Thanks. Unfortunately  no , did not concat as expected.