I have a workflow that is joining data from two in-db connections, doing some cleanup of the values, and then resulting in the data in rows 1-6 below. I need to find a way to create the Parent/Child hierarchy in rows 9-29 based on this data.
A few things to note:
1) In the screen shot below, in the first 6 rows, column A rolls up to B, B rolls up to C, C to D, etc.
2) [NULL] values should be skipped so that the child rolls up to the next available parent.
3) The underlying data may change occasionally, resulting in new rows (i.e. new Plants) and/or [NULL] values filled in.
4) The very top value of the hierarchy (Business Units) is created in the workflow.
5) For anyone familiar, this is creating a dimension hierarchy for load into Hyperion ASO (I left off the Alias and Properties columns for this question).
My first attempt was to basically create separate paths for each parent/child pair (e.g. one for Segment to Company, one for Division to Segment, etc.) and then union the results together, but this creates a problem when there are [NULL] values (not to mention, there has to be a better way). I thought something like an iterative macro would work, but all the examples I found had the macro starting at the very beginning and not half way through the workflow.
Thanks in advance!