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
Hi Lobo,
Try like below
aggr(concat(dISTINCT STATUS,'; ' ),[Customer])
Hi @Lobo77
Have you try?
Concat(Distinct [Status], '; ')
@MayilVahanan thanks , have tried. It gives me the distinct status for a customer , but not distinct order statues for a customer. See customer 19808 , it has 3 orders , 2 cancelled and one Closed. Distinct only gives me Closed and cancelled. I need to see the status for each distinct order. Thanks
@ArnadoSandoval , thanks. Same issue as above. This gives me the distinct statuses for a customer , but not the statuses for the distinct orders for the customer. Thanks for looking.
Is this what you are trying to achieve?
Thanks again for looking into this.
Need to show Orders status/s for distinct orders for customer , would look like below: Notice customer 19808 has 3 order , 2 cancelled , and 1 closed.
Customer Expected
186641 Closed
19808 Cancelled; Cancelled; Closed
3438378 Cancelled; Submitted
Hi @Lobo77
I am attaching my Test.QVF application; I am getting the same results, but Can you explain why 19808 shows Cancelled twice, while 3438378 shows Submitted only once instead of many times?
Thanks.
It's shows many times as an 'order' may have many oder actions. All 'order actions', will all have the same status. So for each distinct order , I want to show the status. But this is showing for order actions. Hope this makes sense. Sorry cannot check the .qvf as cannot upload server. You got the same result as above?