# 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.

Sabina

Hi Sabina,

Welcome to the Celonis Community.

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.