Hierarchy Table need to be created

[dwarikapanigrah]

Hi All

I have a input table where I have gotall the primary id.

From the each Primary _ID , I need to see respective child . I need to show the child_ID till the point we reach the end.

for eg- we pick ID =1 , so it has 10,11 &12 (as the child).

• Now for 10 it has 7 & 12 (as the child) . Here 7 has got 13 has its child(again check in Table1) and 13 has got 14(again check in Table1). There is no child for 14 (so it would be our final child).
• For 11 we got only child as 16(check in Table1)) and ther is no child for 16.
• For 12 there is no child .

After the completion of 1st iteration , again we would have Primary_ ID =2

• Now for 2 we have child_ID=121, and there is no child for 121 . so our iteration stop there.

In 2nd iteration , we have Primary_ID = 3

the process will go so on.. till we reach E.O.F.

Many thanks in advance !!

[dwarikapanigrah · Accepted solution]

Hi All

I have solve the same with Hierarchy function .

Code-

Master:

LOAD id as Parent_id,

     [Child id] as Child_id

FROM

(ooxml, embedded labels, header is 1 lines, table is Table1);

Hir:

Hierarchy (Child_id,Parent_id,Prev,Value,A,Path,'/',Detpth)

load

Child_id,

Parent_id,

Parent_id as  Prev

resident Master;

Drop table Master;