Skip to main content
Hi, I want to Display the Number of Cases that exist in the Cases Table but not in another Table. Celonis_Table_Comparison847184 2.24 KB For example with the data shown in the picture I want to show 2 since Cases 4 and 5 exist in the Cases Table but not in Table 2. Currently my problem is that when I count the total entries of both tables in separate single KPI fields they show the Correct Values: COUNT (DISTINCT (CASES.ID) = 5 and COUNT (DISTINCT (TABLE_2.ID)) = 3 But as soon as I combine both formulas to get the difference as: COUNT (DISTINCT (CASES.ID) - COUNT (DISTINCT (TABLE .ID)) It returns 0 which of course isnt correct as I would expect to get 2 (5 - 3). After some trying I noticed that when I add both values it returns 6 so it seems that the value for the total amount of cases changes from 5 to 3 which also explains the 0 it returns when subtracting. In the Data Modell Cases.ID is connected with Table_2.ID I have also tried solving it with a Case When Statement. COUNT (DISTINCT (CASE WHEN CASES.ID = Table_2.ID THEN NULL ELSE CASES.ID END)) But it also returns 0. I have tried the solutions from the following posts but all with no luck Identifying identical values in 2 different column of an olap table Payment Term Null/Blank Thanks in advance Sebastian
Hi Sebastian, welcome to the Celonis Community! If you are on the IBC, you can now use the extended functionality of the IN operator: IN now also works with columns. So you can say: COUNT(CASE WHEN "Cases"."Id" NOT IN ( "Table2"."ID" ) THEN 1 ELSE NULL END) It is not required that you have a join defined between the two tables. Cheers David