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

[MayilVahanan]

Hi Lobo,

Try like below

aggr(concat(dISTINCT STATUS,'; ' ),[Customer])

[ArnadoSandoval]

Hi @Lobo77 

Have you try?

Concat(Distinct [Status], '; ')

[Lobo77]

@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

[Lobo77]

@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.

[ArnadoSandoval]

@Lobo77 

Is this what you are trying to achieve?

[ArnadoSandoval]

@Lobo77 

Or this?

[Lobo77]

@ArnadoSandoval 

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

[ArnadoSandoval]

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?

[Lobo77]

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?