Hierarchy Table need to be created

dwarikapanigrah — Wed, 25 Nov 2020 16:16:04 GMT

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 !!

Re: Hierarchy Table need to be created

dwarikapanigrah — Fri, 01 Sep 2017 10:21:38 GMT

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;