Unable to make NULL Value as the selector in an OLAP Field

In the OLAP I can see the particular field has null value, but selecting the same value resulting the OLAP to show no rows.

Hi,

this happens when the NULL value in the OLAP table originates from a missing join partner.

Let’s take a look at an example:

Activity Table:

Case Activity
1 A
1 B
2 C
3 A

Case Table:

Case
1
3

If you use columns of both tables in a OLAP table, you see this:

“Activities”.“Case” “Activities”.“Activity” “Cases”.“Case”
1 A 1
1 B 1
2 C null
3 A 3

Case 2 is not contained in the Case table, so the corresponding row in the Activitiy table does not have a join partner in the left outer join that is used, and therefore, the NULL value is shown.

When you click on the NULL value, the following FILTER statement will be used to filter:

FILTER "Cases"."Case" IN ( null )

As you can see, there is no Activity table involved; the filter only goes on the Case table. As the Case table does not contain any NULL values, the filter filters out every case in the case table. This empty result is propagated to the Activity table, where all entries are removed as well (including all rows without a join partner). This is why you end up with an empty OLAP table.

So you want to filter on the join between both tables. For this, you need to introduce the join of both tables in the dimension where you have the NULL value.
If you are on the IBC, depending on which cluster you are on, you might already be able to use the new BIND function, which introduces the join between the given column and table.
The documentation on that will be available very soon.

In our example, you can use it like this:

“Activities”.“Case” “Activities”.“Activity” BIND(“Activities”, “Cases”.“Case”)
1 A 1
1 B 1
2 C null
3 A 3

We “bind” (=join) the Case column of the Case table to the Activity table.
If you now click on the NULL value in the third dimension, the FILTER query looks like this:

FILTER BIND("Activities", "Cases"."Case") IN ( null )

As this filters on NULL values after the “Cases”.“Case” column was joined to the Activity table, you will see one row after filtering:

“Activities”.“Case” “Activities”.“Activity” BIND(“Activities”, “Cases”.“Case”)
2 C null

If you are on an on-prem CPM4 version of Celonis, BIND is not included yet. In that case, you can still force the join, e.g. by using a CASE WHEN statement which always returns the same value. By using the other table (Activity table) in the WHEN condition, the join is introduced:

CASE WHEN "Activities"."Activity" IS NULL THEN "Cases"."Case" ELSE "Cases"."Case" END

Hope that helps!

Best
David

4 Likes

Can you please give more details on where this condition needs to be updated… do we need to add new column in case table or activity table for this ? or do we need to update this logic in OLAP table… also if I want to do drill down for all Null vendor values what needs to be done ?