Skip to main content
Solved

VERTICA LISTAGG function

  • August 25, 2021
  • 7 replies
  • 40 views
A
  • Anonymous

Hey Celopeers community!

 

I am attempting to create a change activity based on a changing list of values, below is an example of a case where a change activity should be created.

 

Case ID | Date | Value

1 | 2021-8-20 | A

1 | 2021-8-20 | B

1 | 2021-8-20 | C

1 | 2021-8-21 | A

1 | 2021-8-21 | B

 

As you can see the list of values changed from A, B, C on 8/20 to A, B 8/21

 

I looked at the VERTICA function library and found LISTAGG function that would help solve this by concatenating fields into one single row, but, unfortunately, it is not enabled in Event Collection.

 

Are there any workarounds you can suggest?

 

Best answer by janpeter.van.d

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

7 replies

janpeter.van.d
Level 12
Forum|alt.badge.img+26
  • janpeter.van.d
  • Level 12
  • 496 replies
  • August 26, 2021

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

A
  • Anonymous
  • 0 replies
  • August 26, 2021

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?

janpeter.van.d
Level 12
Forum|alt.badge.img+26
  • janpeter.van.d
  • Level 12
  • 496 replies
  • Answer
  • August 27, 2021

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

A
  • Anonymous
  • 0 replies
  • August 31, 2021

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

janpeter.van.d
Level 12
Forum|alt.badge.img+26
  • janpeter.van.d
  • Level 12
  • 496 replies
  • April 4, 2022

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

P
Level 2

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.

janpeter.van.d
Level 12
Forum|alt.badge.img+26
  • janpeter.van.d
  • Level 12
  • 496 replies
  • June 1, 2023

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.


Community Code of ConductAccessibility statement