Comparing 2 Table Contents

Hi,
I want to Display the Number of Cases that exist in the Cases Table but not in another Table.

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 isn’t 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