1

Given the following dataset below I would like to create a calculated field that will show me the Employees that are in the same location as their manager. So for example Employee ID 1 would be in the same location as their Manager (Employee ID 5) since they both are located in New York. Conversely Employee ID 2 would not be in the same location (London) as their Manager (Employee ID 5 located in New York).

Employee ID Manager Employee ID Location
1 5 New York
2 5 London
3 6 Miami
4 6 London
5 New York
6 London
harcot
  • 317
  • 1
  • 4
  • 7

2 Answers2

0

You can add a new column like Location_flag in your custom SQL Query data source and use it in Tableau desktop.

Below is the SQL script:

create table ##emp_loc(
emp_id int,
mgr_id int,
location varchar(100)
) ;

insert into ##emp_loc values 
(1,5    ,'New York'    )
,(2,5   ,'London'      )
,(3,6   ,'Miami'       )
,(4,6   ,'London'      )
,(5,null    ,'New York')
,(6,null    ,'London'  );

select a.*,case when b.emp_id is not null then 'true' else 'false' end as Location_flag
from ##emp_loc a
left join ##emp_loc b 
on a.mgr_id = b.emp_id
and a.location = b.location;
Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Pravas patra
  • 1
  • 1
0

You can model this relationship in your Tableau data source, rather than in SQL. Drag the Employee table into the data source model twice, define a relationship between both instances of the table where the Manager ID from the first (left) instance equals the Employee Id in the right instance. I’d rename the second (right) instance Manager instead of Employee in the data model. I’d rename the fields accordingly to, say manager Location

You can optionally update the model performance attributes to tell Tableau that it is a Many-to-One relationship (each employee has at most one manager, but a manager may be related to many employees)

Then you can define a boolean calculated field as [Location] = [Manager Location]. I like to name boolean fields with a trailing ? Mark, so a name like [Colocated with Manager?] makes things pretty clear

Alex Blakemore
  • 11,301
  • 2
  • 26
  • 49