Data rearrangement based on specific structure

Question

Hi,

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

Here is my input data:

Level1IDLevel1NameLevel2IDLevel2NameLevel3IDLevel3NameMEMiddle EastAEEmiratesAE01Emirates 01MEMiddle EastAEEmiratesAE02Emirates 02MEMiddle EastAEEmiratesAE03Emirates 03AMAmericasCACanadaCA01Canada 01AMAmericasCACanadaCA02Canada 02AMAmericasARArgentinaAR01Argentina 01AMAmericasBRBrazilBR01Brazil 01EUEuropeGBGreat BritainGB01Great Britain 01EUEuropeGBGreat BritainGB02Great Britain 02GBLGlobal  ES01Spain 01GBLGlobal  CY02Cyprus 02

and required output:

LevelIDAboveBelowLevelME AELevel1IDCAAMCA01, CA02Level2IDAM CA, AR, BRLevel1IDAE01AE Level3IDAE02AE Level3IDAE03AE Level3IDEU GBLevel1IDGBL ES01, CY02Level1IDAEMEAE01, AE02, AE03Level2IDARAMAR01Level2IDBRAMBR01Level2IDGBEUGB01, GB02Level2IDCA01CA Level3IDCA02CA Level3IDAR01AR Level3IDBR01BR Level3IDGB01GB Level3IDGB02GB Level3IDES01GBL Level3IDCY02GBL 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.

The1804 · Answer

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.

Solution.yxmd

atcodedog05 · Answer

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 🙂