Identifying identical values in 2 different column of an olap table

Hello,
I am trying to identify identical strings in 2 different columns of an olap table.
I would like my formula to return a ‘1’ for every string in the first column that can be found in the 2nd column and a ‘0’ for all the strings from the first column that can’t be found in the 2nd column.
Please note that the relationship is m-n and the values are spread throughout the columns.
This is easily done in a excel but I need to do it in pql.

Thanks in advance,
Sabina

Hi Sabina,

Welcome to the Celonis Community. :slight_smile:
Sorry about the delay in answering your ticket.

We don’t currently have a function implemented that can compare columns so this solution isn’t as elegant as in Excel but it should do the trick.

The ‘simple solution’ returns a 1 for a match a NULL otherwise. If it’s important to you that non matches are given a 0 then please use the ‘complicated solution’ instead.

Simple Solution:

  1. Make an OLAP table
  2. Make the two columns you want to compare dimensions.
  3. Make the following KPI:

CASE WHEN “table1”.“column1” = “table2”.“column2”
THEN 1
ELSE 0
END

  1. Hide the dimension containing the second column by clicking on eye icon so it looks crossed out.

Complicated solution:
Steps 1,2 and 4 are the same as in the simple solution.

  1. Make the following KPI:

CASE WHEN ISNULL(CASE WHEN “table1”.“column1” = “table2”.“column2”
THEN 1
ELSE 0
END) = 1
THEN 0
ELSE (CASE WHEN “table1”.“column1” = “table2”.“column2”
THEN 1
ELSE 0
END)
END

What this double Case when statement does is check when the inner case when statement returns a NULL (i.e. no Match between column 1 and 2) and it is a Null then the outer case when returns a 0 otherwise it uses the result of the inner case when statement.

Please note that this solution requires the two column entries to belong to the same case for them to count as a match. If this is a problem for you or the solution doesn’t work for you for another reason, please write back with more information. It would be helpful to have screen shots of any error messages, more information about the structure of your data model explaining how column 1 relates to column 2. Furthermore, if you are trying to add any other columns to the OLAP table it would be helpful to know the codes of those.

Best wishes,

Calandra