How to check if a value exists in another column of a same table in OBIEE?

Amitkumar Urankar

How do I check if EmpID EE# exists in SupervisorID column and categorize it based on Manager or Individual Contributor in OBIEE.

Here's the table for reference. Currently, consider there are only 2 columns EmpID EE# and SupervisorID. I need a formula to categorize EE's record as "Manager" if EmpID is found in SupervisorID and "Individual Contributor" if EmpID is not found in SupervisorID column.... as shown in column 3.

In Excel I'm using a countif function to verify if the EmpID EE# is found in the SupervisorID field.

i. If the count is greater than zero than we categorize the EE’s record as “Manager”.

ii. If the count is zero then the EE’s is categorized as “Individual Contributor”.

Gianni Ceresa

OBIEE works with SQL queries (assuming your source is a database), so how would you solve this one in SQL?

You will probably to a subquery or a join, there you go for a solution to this in OBIEE as well.

Model is correctly and you will get what you need.

If you model it with a self-join in the RPD you can have a flag column "is manager" being 1 or 0 based on the situation.

If you model it as a parent-child hierarchy (because that's what it is in the end), you have the info if the member is a leaf in your tree or not.

It's all about modelling things based on your functional needs.

Amitkumar Urankar

Thanks Gianni!

I tried it with below query, and it kind of worked to some extent, but still not fetching expected results.

case when "Worker"."Person Number" in (select "Manager"."Person Number" from "Workforce Management - Worker Assignment Real Time") then 'Manager' else 'Individual Contributor' end

Is there any possibility that I can tweak this above query to fetch expected results?