Challenge #1 Employees transition between departments using the Sankey chart

[Rasha]

In an organization for software development, some employees make a decision to move to another department to gain more experience and help in the implementation and improvement processes,

As an end user, I need to track and have a complete picture of the flow between the organization’s departments

Can you help me get this data insight??

I would like to see a Sankey chart showing the movement of the employees between departments in an organization based on the employees' count I believe it should be something like this:

You can use the below-attached data sets files:

1-employees.csv

2-department.csv

3-dept_emp.csv

Also, you can check this article as an example for the Sankey chart

https://docs.incorta.com/5.2/references-visualizations-sankey#sankey-insight-example

Please try it, and attach here the outputs(schema and the dashboard) I Hope you enjoy it 😊.

[zhong]

We can also write SQL queries to generate the transitions in Incorta.    

My results: 

Steps:

1. upload all files, create schema and add all three tables to the schema 
2. add Incorta SQL Table and create table with tree columns (dept_name, new_dept_name, emp_no_count) by writing SQL query.  
3. build insight on top of the generated Incorta SQL table, choose insight type as Sankey, drag dept to SOURCE, new_dept_name to TARGET, and emp_no_count to MEASURE. 

Query for step 2: 

With dept_emp_comb as (
				SELECT
					dept_emp.emp_no,
					dept_emp.dept_no,
					dept_emp.from_date,
					dept_emp.to_date,
					dept.dept_name
				from
					sch_CommunityChallenge1.dept_emp as dept_emp
					inner join sch_CommunityChallenge1.department as dept on dept_emp.dept_no = dept.dept_no
),
transitions as (
				SELECT
					table1.dept_name as dept_name,
					table1.emp_no as emp_no,
					table2.dept_name as new_dept_name
				from
					dept_emp_comb as table1
					inner join dept_emp_comb as table2 on table1.emp_no = table2.emp_no
					and table1.from_date < table2.from_date
)
SELECT
	dept_name,
	new_dept_name,
	COUNT(emp_no) as emp_no_count
FROM
	transitions
GROUP BY
	dept_name,
	new_dept_name
ORDER BY
	dept_name;