Pull and Aggregate distinct string values

Dear Colleagues,

can you please let me know if it is possible to have the distinct string PU function applied in Celonis?
In my specific case I need to know what currencies the vendors have used in the past. I can easily do the Count Distinct, which gives me the number of currencies, but I also wish to know what were these currencies. With PU_STRING_AGG I get the full list from all the documents, but I only need a string of distinct values - in my case USD and PEN. Can it be achieved?
Of course I could create a separate Olap with currency as dimension, but the point is that this table needs to be used in Action Engine, so that is why I need it in single OLAP.

Thank you in advance for any suggestions.

1 Like

Hi,

I think you can use INDEX_ORDER to only select the first currency. This would look like this:

PU_STRING_AGG("LFA1",  "BSEG"."PSWSL", ',' ,  
INDEX_ORDER(  CASE WHEN DATE_BETWEEN(...)=1 THEN  "BSEG"."PSWSL" ELSE NULL END, GROUP( "LFA1"."Key", "BSEG"."PSWSL" )  ) = 1, 
order by "BSEG"."PSWSL" desc )

“LFA1”.“Key” is the primary key of the LFA1 table, so the column that uniquely identifies the vendor. If this consists of multiple columns, you can write all of them inside the GROUP statement.

Hope this works!

Cheers
David

Thank you very much for your suggestion. I will try to apply it as per your proposal and hopefully get the desired result. Once again thank you.