RE: Two Columns with Hierarchy Data needs to be split into separate columns

I have a data set that contains one first column all the names of the employees and on the second column all the managers.

What I am trying to achieve: Manager columns include multiple hierarchy and I want to split them into multiple columns.

I have attached the sample dataset and output in the workbook.

I had previously asked this question on the forum with a smaller dataset and although the solutions I received were great, they didn't help me get the desired result for the original dataset.

Any help is appreciated

Hierarchy Data and Desired Output.xlsx

Workflow

Regex

Output

Custom Tools

Parse

Macros

Preparation

Accepted answers

KilianL

Hi @partht ,

nice challenge. You can find my solution attached. You can extend it with more Joins if the hierarchy goes further. This could also be made dynamic by creating an iterative macro for the joins.

It looks like your desired output removes a couple of people, for example other top managers like Greg, Pete, and Shawn. And there are also 3 employees without a manager: Aman, David, and Kyle. You can see this in my example.

Let me know if this doesn't work, and if it does, please mark this as the solution to help others find solutions quicker.

Kind Regards,
Kilian
Solutions Engineer - Alteryx

manager-employee_community.yxzp

All comments

KilianL

Hi @partht ,

nice challenge. You can find my solution attached. You can extend it with more Joins if the hierarchy goes further. This could also be made dynamic by creating an iterative macro for the joins.

Let me know if this doesn't work, and if it does, please mark this as the solution to help others find solutions quicker.

Kind Regards,
Kilian
Solutions Engineer - Alteryx

manager-employee_community.yxzp

partht

Hey @KilianL ,

Thank you for your solution. I am unable to check it as it is in the newer version of Alteryx. I had previously tried joins in combination with cross tab and transpose.

But the resulting output only kept the employees that were Managers, for some reason, it didn't include the end employees.

Later I tried the "Full Outer" join process with the "Join multiple" tool and it gave me the result that I wanted. It was kind of challenging as I didn't have the "Employee Id" and "Manager Id" for which there are multiple solutions and models on the internet.

Regardless, I would love to take a look at your solution, would you mind pasting the snip of the workflow.

Best,

Parth

KilianL

Hi @partht ,

You can still open my workflow, here is how:

1. unpack the yxzp like any other zip file -> right-click -> unpack

2. open the workflow yxmd with a notepad and change the Alteryx Version at the top of the XML to your version (<AlteryxDocument yxmdVer="2021.1">)

3. you can open the workflow now

partht

Perfect. That worked. Thank you @KilianL

Quick Links

This months top contributors

mceleavey 382

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262