Hi Team,
I am working on pulling sum of values from a particular table column in Celonis using PQL. I want the sum of distinct values of the table column. However, when I do SUM(DISTINCT Table.Column) it is giving me sum of all the values in the column and not the unique ones.
Is there any way in which I can find the sum of unique values of a table column?
Thanks in advance
Regards,
Soham Potdar
Hi Soham,
have you tried using the following statement?
SUM (COUNT ( DISTINCT table.column ))
this formula should first count the distinct values of a column and then sum all of them without considering the duplicates.
Regards,
Carlo Alberto
Hi Soham,
there is no possibility to sum distinct values. I would recommending "deduplicating" this data in the Event Collection in a transformation and then you can apply the regular SUM() function.
Best
Kevin
@carlo.alberto.visen your solution wouldn't work, unfortunately. Let's consider this case
100
100
200
400
The Sum Soham would want is 100 + 200 + 400 = 700. Counting the distinct values would result in 3.
Best
Kevin
Hello Soham,
there is no such function SUM(DISTINCT) as in SQL , so I submitted a product enhancement request, will keep this updated in case it gets approved.
Best,
Gabe
@carlo.alberto.visen your solution wouldn't work, unfortunately. Let's consider this case
100
100
200
400
The Sum Soham would want is 100 + 200 + 400 = 700. Counting the distinct values would result in 3.
Best
Kevin
Can you Help me how to deduplicate the Values in Celonis ...?
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
