need to create this hierarchial output

[informdaya@gmail.com]

i have a input like this
SRC_KEY SRC_NK SRC_NAME SRC_PRNT_KEY
1 A Name_A 0
2 B Name_B 1
3 C Name_C 2
4 D Name_D 3
where pk is first two column.

output will be like this

ID NAME HIRCH_LVL PRNT_ID PRNT_NAME PRNT_LVL REP_LVL
1 A 1
2 B 2 1 A 1 1
3 C 3 1 A 1 2
3 C 3 2 B 2 1
4 D 4 1 A 1 3
4 D 4 2 B 2 2
4 D 4 3 C 3 1


please let me know how i can get this one.
thanks.

[harishkumar.upadrasta]

you can try this.

Use the below combination of data for 2 links, one source and another for reference.
Source Data.
SRC_KEY ,SRC_NK ,SRC_KEY
1 A 1
2 B 2
3 C 3
4 D 4
Reference Data:
SRC_KEY_1 ,SRC_NK ,SRC_KEY
1 A 1
2 B 2
3 C 3
4 D 4

Use a Full outer join on the data.

SRC_KEY ,SRC_NK ,SRC_KEY_ref,SRC_KEY_1 ,SRC_NK_1 ,SRC_KEY_ref
1 A 1 1 A 1
1 A 1 2 B 2
1 A 1 3 C 3
1 A 1 4 D 4
2 B 2 1 A 1
2 B 2 2 B 2
2 B 2 3 C 3
2 B 2 4 D 4
3 C 3 1 A 1
3 C 3 2 B 2
3 C 3 3 C 3
3 C 3 4 D 4
4 D 4 1 A 1
4 D 4 2 B 2
4 D 4 3 C 3
4 D 4 4 D 4


now use a transformer use the following filter condition.

If ( (src_key=src_key_1) and (src_key=1))
and (src_key<src_key1)

then allow the record to flow to target else drop it.

so now your output will be

1 A 1 1 A 1
2 B 2 1 A 1
3 C 3 1 A 1
3 C 3 2 B 2
4 D 4 1 A 1
4 D 4 2 B 2
4 D 4 3 C 3


while printing the SRC_KEY_1 ,SRC_NK_1 ,SRC_KEY_ref fields check
if SRC_KEY= SRC_KEY_1 then set the output of the above fields as '' so that it'll print only 1 A 1

[informdaya@gmail.com]

Hi Hari,
i made it though 8.5 loop variables.
i understand ur logic. I will try this today and let u know if it is working.

thanks buddy.

[ray.wurlod]

I don't believe U (one of our posters from Singapore) would care.

The second person personal pronoun is spelled "you" and, in its possessive form, "your".

DSXchange is not a mobile phone; there is no need to use SMS abbreviations, as there is no practical limit to the number of characters in a DSXchange post.