Counting only duplicate invoices & then only invoices that occur once

#1

Hello,

I am trying to count the number of invoices that are duplicates in our data and then the amount that does not have any duplicates. Distinct count, I don’t believe will tell me which is which.

I have the below script and am trying to use the ‘having count’ piece to only include the invoices that occur more then once, but am getting an error.

COUNT(CASE WHEN “CASES”.“FIMM” in (‘FI’)

AND “CASES”.“CompanyCode” in (‘DE04’,‘GB07’,‘IT01’,‘ES00’,‘ZA12’)
AND HAVING COUNT “BSEG”.“BELNR” > 1

THEN “BSEG”.“BELNR” ELSE NULL END)

Please help.

0 Likes

#2

Hi Anna,
the HAVING clause does not exist in PQL (the Celonis query language). You might have stumbled across this clause as it exists for SQL which you can use to query generic relational databases. For Celonis you need to use a pull-up function:

COUNT(
CASE WHEN “CASES”.“FIMM” in (‘FI’)
AND “CASES”.“CompanyCode” in (‘DE04’,‘GB07’,‘IT01’,‘ES00’,‘ZA12’)
AND PU_COUNT("CASES", “BSEG”.“BELNR”) > 1
THEN “BSEG”.“BELNR” 
ELSE NULL 
END
)

Best regards,
Pol

0 Likes