topic Re: Challenge #1 Employees transition between departments using the Sankey chart in Incorta Conundrums

Challenge #1 Employees transition between departments using the Sankey chart

Rasha — Fri, 18 Nov 2022 16:06:38 GMT

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

Re: Challenge #1 Employees transition between departments using the Sankey chart

marwansalem — Wed, 23 Nov 2022 10:58:54 GMT

Here is my output.

Upload the CSV files, create a schema and create a table from each one.
Create Materialized View (MV) that generates all the employee transitions.
Create another table that shows the total number of employees that moved from one department to another (EmployeeTransitionSummary)
Create a new insight, and use Dept Name as the source, New Dept Name as the target, and Employee Count as the measure. Optionally sort the source field by Employee Count. (We can also use Insight over Insight instead of creating a separate table for EmployeeTransitionSummary)

Now for the Materialized View in step#2, here is the PySpark script for EmployeeTransitions.

from pyspark.sql.window import Window from pyspark.sql.functions import lead dept = read('CommunityChallenge1.department') dept_emp = read('CommunityChallenge1.dept_emp').withColumnRenamed('dept_no', 'employee_dept_no') # join with department table to include department name dept_emp = dept_emp.join(dept, dept.dept_no == dept_emp.employee_dept_no).drop('employee_dept_no') # partition by the employee number and order by start date "from_date" windowSpec = Window.partitionBy("emp_no").orderBy("from_date") # use lead function to get the "next" department name transitions = dept_emp.withColumn('new_dept_name', lead("dept_name", 1).over(windowSpec)) transitions = transitions.filter(transitions.new_dept_name.isNotNull()).sort('emp_no') save(transitions)

You can learn more about windows functions in PySpark here.

Re: Challenge #1 Employees transition between departments using the Sankey chart

john_cross — Wed, 23 Nov 2022 22:01:26 GMT

I am solving this challenge using the power of Incorta's no-code/low-code capabilities.

1.) Upload the CSV files, create a schema and create tables for the department.csv and dept_emp.csv files.

2.) Create aliases of department and dept_emp.

3.) Join dept_emp to the department on the dept_no. Join dept_emp to its alias with the below joins. Join the dept_emp alias to the department alias on dept_no.

4.) Add a Formula Columns to the dept_emp table with the Column Formula of 1. We are creating this because at this time, the Sankey only supports sums and not counts.

5.) Create an Aggregate Table insight. Put the Dept Name from the department table in the Grouping Dimension. Put the Dept Name from the department alias table in the Grouping Dimension below the other Dept Name, I recommend renaming it not to confuse the two. The formula column you created should be in the measure. To filter only on transfers, grab any column from the dept_emp alias table, place it in the Individual Filter, and only show Not Null records.

6.) Change the Insight into a Sankey and add the formula column you created as the Sort By of the Dept Name in the Source.

Here is the diagram of my Sankey schema for this challenge.

The join between the dept_emp and alias of dept_emp only works because the to date from the previous department matches the from date of the new department. If this was different we would have to create a different solution using Incortas First Version filter or SQL/Pyspark.

Re: Challenge #1 Employees transition between departments using the Sankey chart

LukeG — Fri, 27 Jan 2023 02:33:39 GMT

Here is a video demonstration of the solution @john_cross provided above. Hope this is helpful!

https://www.youtube.com/watch?v=uTfXWgtvJE8

Re: Challenge #1 Employees transition between departments using the Sankey chart

JoeM — Fri, 27 Jan 2023 15:47:06 GMT

Awesome video! Thanks @LukeG !

Re: Challenge #1 Employees transition between departments using the Sankey chart

zhong — Thu, 23 Mar 2023 19:45:05 GMT

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

My results:

Steps:

upload all files, create schema and add all three tables to the schema
add Incorta SQL Table and create table with tree columns (dept_name, new_dept_name, emp_no_count) by writing SQL query.
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;