Expanding Hierarchical Data

[logical_pea_20]

Hi,

I am trying to take hierarchical data and expand it out for each "level" in the hierarchy based on a legend. My initial thought was to somehow run it through an iterative macro, counting down the level until I reach the bottom layer of the hierarchy. I am having some trouble wrapping my head around if this approach makes sense and how to append the entries together. Please see below an example of my data, my desired result, and my rough attempt at creating the workflow needed.

I would greatly appreciate any help or ideas on how to transform this data. Thanks.

Input data (simplified IDs for demonstration)

ID	Value	Level	Immediate_Parent
1-4	91	4	1-3
2-6	56	6	2-5

Desired result (simplified IDs for demonstration)

ID	Value	Level	Immediate_Parent
1-4	91	4	1-3
1-3	91	3	1-2
1-2	91	2	1-1
1-1	91	1
2-6	56	6	2-5
2-5	56	5	2-4
2-4	56	4	2-3
2-3	56	3	2-2
2-2	56	2	2-1
2-1	56	1

Attempt

[apathetichell]

I'd use generate rows to get a range. Regex to subtract a number from the second digit based upon the range count and to clean up the last value - and a multi-row formula to slot them down one.

Probably would need a select tool in practice to increase the size of the string field before the regex. I wouldn't recommend using an iterative macro for this.

[Qiu]

@logical_pea_20 
The solution provided by @apathetichell  is already perfect in my opinon and I only add one formula tool to set the top level immediate parent to null.

[logical_pea_20]

Thanks for your response! I should have made this clearer but the actual IDs would be more complicated in practice with no reliable pattern to run a RegEx on. I just wanted to demonstrate the idea since the actuals IDs are more difficult to wrap one's head around. The difficulty I was having was searching through a hierarchy to replace the ID and Immediate_Parent values.

In practice the IDs would look like this:

ID	Value	Level	Immediate_Parent
130100000	91	4	130000000
110000000	56	6	100000000

And the legend would be like this:

ID	Level	Immediate_Parent
000000000	1
210000000	2	000000000
230000000	3	210000000
230100000	4	230000000
000000000	1
210000000	2	000000000
230000000	3	210000000
230200000	4	230000000
100000000	5	230200000
110000000	6	100000000

The desired result would be the same (with more complicated IDs):

ID	Value	Level	Immediate_Parent
230100000	91	4	230000000
230000000	91	3	210000000
210000000	91	2	000000000
000000000	91	1
110000000	56	6	100000000
100000000	56	5	230200000
230200000	56	4	230000000
230000000	56	3	210000000
210000000	56	2	000000000
000000000	56	1

In this case, would it make sense to run the data through a macro?

[apathetichell]

Yes. - that's more of an iterative macro. The existence of a 1-1 match in a legend changes this entire process. The way how you described it originally the rows were built out via an internal category (the range). Having the rows preexisting and with a unique set of values makes this a bit different. Note - if you had a key like you do in the first - with a numeric value which is static, I might recommend some combo of tile tool self-join.