I also add a little different approach => what if the 'levels' is unknown amount?
First i create one solution what is practically is the same as Vladi solution (what is the most cleanest and fast solution).
And below that i add another solution, what handle in theory the case of unknown (unlimited) amount of CategoryTypes (parent/child relation).
First i wondering to use some ARRAY way of solving, and also think about the Rollup.
But for curiosity i choose a 'looks difficult' approach: more component and less code based, and use DeDup components mainly.
I like to highlight: my solution is not so effective (especially performance wise, and so on), and may possible to do in less step, but at general it may show some DeDup usage for logical cases. Main logic steps:
- First i get the 'level' names to 'level' numbers, and join back.
- Calculate for each row some 'group_id' (x_groupid) (to keep the same level CategoryTypes under one 'groupid', its later helps to find out the last row in the same group (what may the parent of some child)
- DeDup the rows, to get only the 'last' rows in group, and use it later as possible 'parent' value
- Join back to 'original' rows the possible 'parent rows' cases (connect by logical 'level') => in here we get ' lot of not good result' rows also, what we handle in next steps.
- We filter out rows, where we know the joined parent cant be the correct one
- DeDup the remained rows, to get back the 'nearest' parent
- And we done.
Please see the attached Graph+Sample Excel (graph and code inside is commented in some level).