Hi Eugene Em,

Maybe there is a workaround, but that one is a bit limited since it is a join of all the rows that you want to take into account. In other words, you might need 10 joins or more, and it doesn't grow with your data.

WITH VALUE AS (

  SELECT

    "Case ID"

    , "Date"

    , "Value"

    , ROW_NUMBER() OVER (PARTITION BY "Case ID", "Date", ORDER BY "Value" ASC ) AS SEQ

  FROM <TABLE_NAME>

)

SELECT

  "V1"."Case ID"

  , "V1"."Value" || "V1"."Value" || <...> || "Vn"."Value" AS "ACTIVITY_EN"

  , "V1"."Date" AS "EVENTTIME"

FROM VALUE AS V1

LEFT OUTER JOIN VALUE AS V2 ON

  "V1"."Case ID" = "V2"."Case ID"

 "V1"."Date" = "V2"."Date"

......

LEFT OUTER JOIN VALUE AS Vn ON

  "V1"."Case ID" = "Vn"."Case ID"

 "V1"."Date" = "Vn"."Date"

WHERE 1=1

  AND "V1"."SEQ" = 1

  AND "V2"."SEQ" = 2

.....

AND "Vn"."SEQ" = n

;

Hope it helps!

Best regards,

Jan-peter

So we are essentially transposing the rows into columns, is that the idea?

I'm assuming the output would look something like this:

Case ID | Date | SEQ 1 | SEQ 2 | SEQ 3 | ... | SEQ n

1 | 2021 - 08 - 20 | A | B | C

1 | 2021 - 08 - 20 | A | B |

If so... this might just work. I would need to make sure that this is scalable and the number of self joins is always synced with the max number of values stored for the Case ID/Date combo, which is a whole other can of worms.

Are there any plans to expand the number of VERTICA functions enabled in Event Collection?

Not exactly, I could indeed provide some more insights in the query.

What you are doing, iterating over the rows, that has the same Case ID and Date, and provide them a sequence number:

ROW_NUMBER() OVER (PARTITION BY "Case ID", "Date", ORDER BY "Value" ASC ) AS SEQ

We do this in a 'With Clause' (see WITH Clause (vertica.com)). The easiest way to explain is that you make a sort of temporary table/view, in this case for each row. In the lower part of the query, we join all the teporary tables in sequence as long as they have the same Case ID and Date:

LEFT OUTER JOIN VALUE AS Vn ON

   "V1"."Case ID" = "Vn"."Case ID" <-- The Case ID/Date of the n'th table should be same as the first table

  "V1"."Date" = "Vn"."Date"

WHERE 1=1

   AND "V1"."SEQ" = 1 <-- Table V1 should have row sequence 1 etc.

   AND "V2"."SEQ" = 2

The activity name is created, by joining the values of all the joined tables ('||' is a string join operator).

Note that you have to replace the '...' parts and the values between '<>', but I guess you already found out ;)

Your comment that you should be aware that the number of repetitions should be in sync with the number of joins, is exactly the weak spot of this workaround.

Regarding the support of more advanced Vertica functions in Celonis, I cannot help with that unfortunately, since I'm also not a Celonis Employee. You can always try to ask for a feature request, by creating a case under the button 'customer support' at top of this website.

Good luck with your use case!

Cheers,

Jan-peter

Thanks Jan-Peter, your response is extremely helpful

For all readers that joined later in this conversation: LISTAGG is now supported. https://help.celonis.cloud/help/x/Bk4GAw

And when will it available: WITHIN GROUP ORDER BY clause of listagg function? I read it is available in Vertica 11.0.2. But I dont know wich version I have in my EMS.

This is hard to say since not all Vertica functions are automatically available in Celonis. If this is really a function you need I would advice to send a service request to see what the possibilities are.