I want to transform values for example „Materialnumber“ which are in a Column group by „HandlingUnit“ order by „Position“ into a string of comma-separated values (see example)

Hi @fabio.formi - this is a perfect use case for the PU_STRING_AGG within our execution instruments.

Consider an OLAP table with the following Columns:

Client, Sales Document, Line Item, Material

Where one Sales Document can be associated with many materials. To aggregate the materials to the Document-level, we could use

PU_STRING_AGG(SALES_DOC, SALES_DOC_LINE, ';')

Where the last argument is the delimiter. The results are contained in the screenshot below. Hope this helps!

Hi @greg.zajac - thank you very much for your answer. This helps me a lot in the environment of the process analytics.

In my case I need this functionality for pretransform tables in the event collection in order to build up keys for joins or comparisons....etc.

Do you know if there are solutions also for the SQL workbench?

Again, a big thank for your help and support.

Fabio

Hi @fabio.formi - I'm glad that I was able to help. Unfortunately, I do not know of any solutions in the SQL workbench directly. There is a workaround to this, though:

1. Create a data model with only the table containing the handling unit, position, and material number.
2. Create an analysis with an OLAP table using the PU_STRING_AGG method I described above.
3. Pull the data into the machine learning workbench using PyCelonis (described here)
4. Push the data back into Event Collection for use in the SQL workbench using PyCelonis (described here)

I hope this helps!

Best,

Greg

Hi @fabio.formi,

Is the Implode function maybe something that you could use? See its documentation here. 

It will return a list with brackets, but I'm sure you can replace them with emty characters.

Best regards,

Jan-peter

Hi @janpeter.van.d ,

thank you very much for your help.

Unfortunately Celonis doesn´t support function "IMPLODE" (it´s the same problem with the function „LISTAGG“

It comes to this Message: " Execution error: Function IMPLODE is not supported".

At the moment I have a workaround for my problem using a concatenation in a first temp. table

SELECT HU_KEY

       ,Behaeltertyp

       ||'_'

       || COALESCE(LEAD(Behaeltertyp) OVER (PARTITION BY HU_KEY ORDER BY POSNR), '')

       ||'_'

       || COALESCE(LEAD(Behaeltertyp, 2) OVER (PARTITION BY HU_KEY ORDER BY POSNR), '')

       AS MATNRCONCAT

and then taking the first value of this temp.table order by the length of the concatetantion in descending order.

SELECT DISTINCT

       HU_KEY

       ,FIRST_VALUE(MATNRCONCAT) OVER(PARTITION BY HU_KEY ORDER BY LENGTH(MATNRCONCAT)DESC) AS MATNRCONCAT

This combination seems to work quite well, but it´s a little bit laborious

Best regards,

Fabio

Hi @fabio.formi,

Sorry to hear that. Glad that you have found workaround, it seems quite impressive to me!

Best regards,

Jan-peter