Calculate 3way match ratio with null values from tables

#1

Dear All,

I am trying to calculate 3way match ratio using Celonis proposed PQL statement as a reference. However in my understanding we are omitting some PO while doing checks on PU_SUM. In case the source EKBE or RSEG table does not contain any record (no GR or no IR has been posted) the PU_SUM gives null result. Is there any nice and easy way to overcome this problem - I have tried using nested CASE WHEN, but the PQL statements gets really complicated because of that. I have attached the sample of what I have done so far.
Thank you in advance for any advice.
3way match with blank GR or IR.docx (188.8 KB)

#2

Hi,

I’m not sure if I understand your question right. You ask “Is there any better way to deal with checks in case the source table contains to GR or no Invoice?” but what does deal mean? Do you want to exclude such cases from the ratio entirely (i.e. it calculated as if they didn’t exist) or do you want them to be given the value 0 so they are included as a case not fulfilling the condition?

I’m also not sure why you’re including the case “EKBE”.”SHKZG” = ‘H’ in the second example but not the first. If you are trying to catch the cases where “EKBE”.”SHKZG” is left empty you could use the following condition: ISNULL(“EKBE”.”SHKZG”) = 1. You can also use ISNULL to catch the cases for which RSEG is empty.

I think using case when statements is the way to go in this case. If it’s important that the code is easy for others to quickly understand then it could really help to write more expressive comments explaining what each section does in plain English. I.e. under “Order Quantity = GR Quantity” you could write “Check if the amount actually ordered lies within the tolerance of the planned order (and was bought on debit)”.

If you have further questions, please feel free to write back to us with further details clarifying what you aim to achieve and we will try to help you further.

Best regards,
Viana

#3

Thank you very much for your answer.
The first example I am providing comes from the 3way P2P analysis from content store. It does not include conditions for GR reversal (EKBE”.”SHKZG” = ‘H’), so in my reworked formula I have included it (as for my business case I also consider as 3way match the scenario where user did the reversal, but in the end the IR qty = GR qty = PO qty).
Regarding the no GR, no IR scenario, I wish to give them value 0 so they are included as a case not fulfilling the condition.
So if we have 3 Purchase orders:
PO1: PO qty: 10, GR qty:10 IR qty:10 - 3way match condition fullfilled - case count =1
PO2: PO qy: 10, GR qty: 5 IR qty: 5 - 3way match condition not fullfilled - case count = 0
PO3: PO qty: 10, GR qty: NULL IR qty: NULL - 3way match condition - NULL
I want to show PO qty match = 33% (1 out of 3 PO has 3way match) instead of PO qty match = 50% (as 3rd PO gets ignorred because of NULL value)

So if CASE WHEN is the most convenient approach, I will continue with it - Thank you for confirming.

#4

Hi,

It sounds like you’ve got it under control but here is a tip about how to make it easier for yourself.

I recommend that you make it easier for yourself by using average (AVG) instead of “100*(1.0*COUNT(DISTINCT” and instead of THEN “EKPO”.”MANDT” || “EKPO”.”EBELN” || “EKPO”.”EBELP” just use THEN 1.0. Since your aggregating on the level of EKPO (which is the case table in P2P processes it is already only counting each case once. This approach enables you to specify that if Values are missing these should be counted as 0.0 rather than NULL, without running into type compatibility issues.

Best Regards,
Viana

#5

Hi,

are you on the IBC or Celonis 4.5? Then you can use the new COALESCE operator instead of the CASE WHEN statements. The COALESCE operator takes two or more arguments (columns or constants) and returns the first of these arguments which is not NULL.

For Celonis 4.4 and earlier, I think you need to use the CASE WHENs.

Best regards
David

#6

Hello,

for the time being we are still on Celonis 4.4. However its good to know about an alternative with COALESCE if / when we will migrate to Celonis IBC.

thank you.

best regards,
Krzysztof