if then loop

[#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 want 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.

Can anyone help me with this in qlikview.

[Gysbert_Wassenaar]

See this blog post: Unbalanced, n-level hierarchies

And this document: Hierarchies

[#3 · Accepted solution]

Hi Kartik,

Please find the qvw attachment.

http://community.qlik.com/docs/DOC-5490

Regards,

Joshmi

[#4]

Thanks Joshmi,

but it is determining reference no. only for the first entry no. in the above example, it has given the output for entry no.1 , it should also show output for entry no. 5 and 6 whose source no is 2 and 3 respectively.

Entry No.	Reference No.	Document No.
8	1	AAA
5	2	BBB
6	3	CCC

[#5]

Hi Kartik,

PFA

[#6]

Dear Joshmi,

One more help. the formula you provided works well. But practically in my company, we have more than one Ref against an Entry.

Test:

LOAD * INLINE [

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

For eg. in the above table, Entry 5 is from 2 and Entry 6 is also from 2.

This is possible when entry 2 has 100 Qty's and 50 are issued under Entry 5 and balance 50 under entry 6.

in such a case, the output should be as follows:

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

please help.