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 think the example will better phrase my question. So here we go: Here is the sample data: EmployeeManagerAlexisCassieNickDaveBenjaminDaveAmandaPheobeJohnPheobeMaryPheobeJackiePheobeTomJustinDaveJacksonPheobeJacksonJustinJacksonCassieJacksonLexJacksonDarleneLexDanielLex And here is the desired output: Manager - IManager - IIEmployeeJacksonDaveNickJacksonDaveBenjaminJacksonPheobeAmandaJacksonPheobeJohnJacksonPheobeMaryJacksonPheobeJackieJacksonJustinTomJacksonCassieAlexisJacksonLexDarleneJacksonLexDaniel Any help or suggestion is appreciated. I tried self joining the data, but I kept losing the employees who are not Managers. Thanks in advance.

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 think the example will better phrase my question. So here we go:

Here is the sample data:

Employee	Manager
Alexis	Cassie
Nick	Dave
Benjamin	Dave
Amanda	Pheobe
John	Pheobe
Mary	Pheobe
Jackie	Pheobe
Tom	Justin
Dave	Jackson
Pheobe	Jackson
Justin	Jackson
Cassie	Jackson
Lex	Jackson
Darlene	Lex
Daniel	Lex

And here is the desired output:

Manager - I	Manager - II	Employee
Jackson	Dave	Nick
Jackson	Dave	Benjamin
Jackson	Pheobe	Amanda
Jackson	Pheobe	John
Jackson	Pheobe	Mary
Jackson	Pheobe	Jackie
Jackson	Justin	Tom
Jackson	Cassie	Alexis
Jackson	Lex	Darlene
Jackson	Lex	Daniel

Any help or suggestion is appreciated. I tried self joining the data, but I kept losing the employees who are not Managers.

Thanks in advance.

Workflow

Regex

Output

Custom Tools

Parse

Batch Macro

Accepted answers

All comments

Neer_0135

Please find attached solution

Solution.yxmd

Neer_0135

Like and mark it as a solution if your problem solved

Amol_Telore

Hey @partht

I did self join and am able to get the desired results. In your question, you have mentioned about employees who are not managers are getting dropped. So Is there any other dataset where you are facing this issue? Please let me know if this solution answers your question.

New Workflow1.yxmd

Qiu

@partht
I referred to work of @Sntrada

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Employee-Manager-org-hierarchy/td-p/599501

0823-partht.yxmd

partht

Hey Guys! @Qiu @Amol_Telore @Neer_0135

Thank you for your responses.

All of your solutions are helpful and solves the problem I presented in the sample data. But the original dataset contains multiple levels of hierarchy.

I am trying to combine and use parts of your solutions to get the desired result.

In case, I don't get it. I will replicate my original data into the sample data and see if you could help me. Thanks All!

Qiu

@partht

Thank you for the feedback.

partht

Hey All!

I am back after a quite a few tries and need further help. I have attached a workbook with Sample input data and desired output.

Please let me know if you guys could create a workflow to result the desired output. Thanks guys and gals.

Hierarchy Data and Desired Output.xlsx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703