OBIEE case statement check on non excistence

Question

I have the following situation:

column 1  column 2  column 3
A           1         1
A           1         2
B           3         4
B           3         5

I need to color my letters when the value in column 2 never occurs in column 3. But I need to color all my letters. Does anyone know how to write a case statement for this?

So I'll explain my example: I dont't need to color the letter A because there is a match between column 2 and 3 and the first row. I do need to color my B's because there is never a match between columns 2 and 3.

I already tried this:

count(distinct(case when "Column 2" != "Column 3" then 1 else 0 end))

but this gives a result for each row and I need a result for the total package.

Thanks!

Answer 1

You can approach this as following:

• Create a logical column on your analysis that does a case statement that returns 1 or 0 depending if the values of column2 and column3 are the same (pretty much like the case-when that you provided on your answer but without the count distinct).

• Wrap that case statement with a MAX grouped by your column1. This will give you either a consistent 1 or 0 across all your different values of column1. You can use this value for your conditional formatting. The key here is to use the aggregated function with the group by.

You have here some oracle documentation on how to use the logical SQL group by.

Hope that helps! Good luck!