Skip to main content

I have the following lines of code for which I would like to create a table in which we see the total amount of transaction codes, another column with total amount of transaction codes per company code and the amount of transaction codes as a percentage of the total transaction codes, per company code.

In the back end I have managed to write these codes, however, because of a lack of the 'Over' function I can't seem to be able to make it work in the front end. 

These are the codes in question that are currently working in the transformation scripts ( back-end ):

select 

TRANSACTION_CODE,

ACTIVITY_EN,

Company_code,

Count(TRANSACTION_CODE) as count_tcode,

SUM(count(TRANSACTION_CODE)) OVER(PARTITION BY Company_code) AS total_per_Company_code,

ROUND((count(TRANSACTION_CODE) * 100.0 / NULLIF(total_per_bukrs, 0)), 2) AS percentage_per_Company_code

FROM _CEL_AP_ACTIVITIES

Group by 

TRANSACTION_CODE,

ACTIVITY_EN,

Company_code

How would you go about creating a PQL script that could tackle these lines of code in for example a KPI within a knowledge model?

 

Thanks in advance!

Hi ​@Maurits de Vries, 

If you add the table component in Studio, you can add the first 3 columns without any adjustments. 
Then add the following columns with your aggregation functions. Celonis will automatically detect that you want to group by the first free columns.
Since in one particular case you do not want to aggregate on the first three fields, but on one specific, you can use the Pull-up functions, see https://docs.celonis.com/en/pull-up-aggregation.html. 
In this case, you can calculate something by aggregating on a specific field, for example:

SUM(count(TRANSACTION_CODE)) OVER(PARTITION BY Company_code)
 

would become:


PU_COUNT(
DOMAIN_TABLE (table_name.company_code), -- defines to which column the results are bounds
table_name.transaction_code) -- defines what should be aggregated

I hope this helps!

Best regards,

Jan-peter

Community Code of ConductAccessibility statement