Hierarchical loop (if then)

[#1]

I have the following table:

Entry No.	Reference No.	Document No.
1		AAA
2		BBB
3		CCC
4	1	DDD
5	2	EEE
6	3	FFF
7	4	GGG
8	7	HHH

Entry No. is a unique Number. Entry nos 1 to 3 are staring numbers, the balance are linked numbers,. for eg. entry no 8 is linked to 7 which in turn is linked to 4, which in turn is linked to 1. Because there is no reference against 1, it is the the starting number

i wanted the results as below:

Entry No.	Reference No.	Document No.
8	1	AAA

For entry no. 8, the links end at entry no. 1 and corresponding reference against 1 is AAA.

i got the output as required in the attachment.

Now i have made a small change.

Entry No.	Reference No.	Document No.
1		AAA
2		BBB
3		CCC
4	1	DDD
5	2	EEE
6	2	FFF
7	4	GGG
8	7	HHH

in the above table, for entry 5 and 6, the reference number is the same.

Now can anyone help me with this revised table.

please help.

[anbu1984]

Can you post the script/expr which you tried ( I am using Personal edition )

[#3]

Test:

LOAD * INLINE [

    eNTRY, Ref, Doc

    1, , A

    2, , B

    3, , C

    4, 1, D

    5, 2, E

    6, 3, F

    7, 4, G

    8, 7, H

];

TREE:

HierarchyBelongsTo(eNTRY, Ref, Doc, 'ancestorid', 'ancestor', 'layer')

load Ref,

eNTRY,

Doc

resident Test ;

TREE_new:

load distinct

*,

Num( layer+1) as NewLayer

resident

TREE ;

drop table TREE;

Straight table - > Dimensions - eNTRY

Expressions 1->ref - (=aggr(Min(ancestorid),eNTRY))

                   2-> Doc = FirstSortedValue(ancestor,ancestorid)

[anbu1984]

With Ref as 2 for Entry No 6, are you not getting doc as B? Or are you expecting different output?

[#5]

I want output as follows:

Entry No.	Reference No.	Document No.
5	2	BBB
6	2	BBB

[anbu1984]

I am getting BBB as Doc with your script and expr. Check the attached app

[#7]

Hi Karthik,

Edit the inline table as

LOAD * INLINE [

    eNTRY, Ref, Doc

    1, , A

    2, , B

    3, , C

    4, 1, D

    5, 2, E

    6, 3, F

    7, 4, G

    8, 7, H

];

Then Reload your qvw. (Ctrl+R).

PFA

[khalander]

Hi Joshmi George,

Thank you it is very useful

[#9]

Dear Joshmi George,

Have one more requirement.

what i have given is a very small sample.

I am using this on my actual database which runs to more than 35 lakh records.

so, when the loop runs, the number of virtual records created is several crores.

is it possible to run this loop only for selected records. assuming i want to run for entry numbers 3100000 to 3100005 for which the source number may be anywhere. but it should run faster and not throw an error message 'Out of Virtual memory'