Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
@Lobo77 try below
=concat(aggr(Concat(DISTINCT Status),Customer,Order),';')
@Kushal_Chawda Thanks. Unfortunately no , did not concat as expected.