Data rearrangement based on specific structure

[The1804]

Hi,

I'm looking for a solution that would help me rearrange columns in a specific way.

Here is my input data:

Level1ID	Level1Name	Level2ID	Level2Name	Level3ID	Level3Name
ME	Middle East	AE	Emirates	AE01	Emirates 01
ME	Middle East	AE	Emirates	AE02	Emirates 02
ME	Middle East	AE	Emirates	AE03	Emirates 03
AM	Americas	CA	Canada	CA01	Canada 01
AM	Americas	CA	Canada	CA02	Canada 02
AM	Americas	AR	Argentina	AR01	Argentina 01
AM	Americas	BR	Brazil	BR01	Brazil 01
EU	Europe	GB	Great Britain	GB01	Great Britain 01
EU	Europe	GB	Great Britain	GB02	Great Britain 02
GBL	Global			ES01	Spain 01
GBL	Global			CY02	Cyprus 02

and required output:

LevelID	Above	Below	Level
ME		AE	Level1ID
CA	AM	CA01, CA02	Level2ID
AM		CA, AR, BR	Level1ID
AE01	AE		Level3ID
AE02	AE		Level3ID
AE03	AE		Level3ID
EU		GB	Level1ID
GBL		ES01, CY02	Level1ID
AE	ME	AE01, AE02, AE03	Level2ID
AR	AM	AR01	Level2ID
BR	AM	BR01	Level2ID
GB	EU	GB01, GB02	Level2ID
CA01	CA		Level3ID
CA02	CA		Level3ID
AR01	AR		Level3ID
BR01	BR		Level3ID
GB01	GB		Level3ID
GB02	GB		Level3ID
ES01	GBL		Level3ID
CY02	GBL		Level3ID

All level values are presented in 1st column (4th column specifies source column from input).

Column "above" shows 1st level to the left from specific value that is not empty (case of ES01 and CY02).

Column "below" groups together all values to the right from specific value (doesn't go all the way down. Only 1 level below)

Some assumptions that might simplify the solution:

• Each level can only cascade up to 1 level but can have multiple levels below.
• There can't be duplicates on any level
• There can be more than 3 levels (up to 10)

Let me know if I can provide any more info.

[atcodedog05]

Hi @The1804 

This weekly challenge might be helpful for your scenario of creating hireacrhy  https://community.alteryx.com/t5/Weekly-Challenge/Challenge-12-Creating-an-HR-Hierarchy/td-p/36740

Hope this helps 🙂

[The1804]

Hi @atcodedog05,

Thanks for the suggestion! I went through some of the provided solutions and it looks like I would have to reverse the challenge to some degree. Unfortunately that is a bit above my capabilities 🙂 Still learning...

I almost managed to get the expected result but I still have trouble figuring out how to skip a level both above and below. Also my solution cannot go further than 3 levels which isn't a big deal - I can manually add more blocks if needed.

If someone could help out with skipping empty levels (having GBL as above in CY02,ES01 and vice versa) I would really appreciate it.

My hacky solution in attachment.