Hierarchial solution needed

[harsha384]

Hi,

I have a table which includes parent ID and individual ID. I need to create hierarchial relation between for each node of the branch.

For example,

If 10 is the topmost parent in the branch,which has multiple child, like 20 and 30, then further 20 has 40,50,60 child. N again 40 has child called 80.

Now to trace the branch of 80,

the set will be (80,40,20,10) bottom to top.

How to create it?

attaching the diagram for further reference

[dataMack]

Would a simple 2 colum parent-child mapping table work?  Something like this:

Parent	Child
10	20
20	40
40	80

You could then self-join to walk 80 back to 10 through the intermediate steps.  You could also add a third column for step # (or node) and make it even easier on yourself to know if its the primary, secondary, tertiatary level of the map as well.

[KaneG]

Hi Harsha,

I think @dataMack's solution is pretty on the money. Check out this thread if you would like to know how to make it into an iterative macro in the case that the set is 50 deep...

Kane

[harsha384]

Hi Kane,

Thanks for your reply.I am still not able to get clear idea about the iteration flow.What is the use of I output in the macro if there is no further use of that data from it?

I have applied the same flow for my data, that is parent ID and ID, but i am only getting output of 1st iteration. Other iteration is throwing an error.Pls guide!

[harsha384]

Hi jason,

Thanks for your reply.I need to trace the root of every branch with every node in it. Is there any solution to it?

ID       Parent_ID    O/P

10          Null                10

20              10              10

30              10              10

40               20             20,10

50               20             20,10

60               20             20,10

70               30            30,10

80               40            40,20,10

[harsha384]

I need output like this, and so on....

ID       Parent_ID    O/P

10          Null                10

20              10              10

30              10              10

40               20             20,10

50               20             20,10

60               20             20,10

70               30            30,10

80               40            40,20,10

[KaneG · Accepted solution]

Hi Harsha,

The Macro in that other thread is an iterative macro, meaning that the data going to the I output iterates through the macro again. Check out the Iterative Supply & Demand sample under Help > Samples to see a working Iterative Macro.

If there is only 3-4 levels deep in your data, then I wouldn't bother with the macro, but instead just use @dataMack's suggestion a couple of times.

Kane 

[harsha384]

Hi Jason,

Thanks for your reply,I didnt get the clear idea about how should i get the flow from root to head?

A solution to it will be most helpful if you can @dataMack

Harsh

[dataMack · Accepted solution]

Hi Harsha - Here's a simple workflow to better explain my approach.

If you start with the 2 column Parent-Child mapping table as your source (this keeps thinks simple since you only have to store the single hops) then you would just join the Parent to Child columns - both left and right inputs of the join tool are sourced from the same table.  People will sometimes call this a 'self-join'.  You would repeat that process for however many possible levels you have in your hieracrcy, or as was also suggested by @KaneG you could do this in a macro to be more efficient. 

I chose to repeat each step to better demonstrate for you how it works:

[harsha384 · Accepted solution]

Thank you Jason! A simple and easy solution:)