Expanding Hierarchical Data

Question

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)

IDValueLevelImmediate_Parent1-49141-32-65662-5

Desired result (simplified IDs for demonstration)

IDValueLevelImmediate_Parent1-49141-31-39131-21-29121-11-1911 2-65662-52-55652-42-45642-32-35632-22-25622-12-1561

Attempt

apathetichell · Answer

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.

logical_pea_20 · Answer

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:

IDValueLevelImmediate_Parent130100000914130000000110000000566100000000

And the legend would be like this:

IDLevelImmediate_Parent0000000001 2100000002000000000230000000321000000023010000042300000000000000001 21000000020000000002300000003210000000230200000423000000010000000052302000001100000006100000000

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

IDValueLevelImmediate_Parent230100000914230000000230000000913210000000210000000912000000000000000000911 110000000566100000000100000000565230200000230200000564230000000230000000563210000000210000000562000000000000000000561

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

Qiu · Answer

@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.

hierarchy workflow-r1.yxmd