topic Re: Hierarchical loop (if then) in QlikView

Hierarchical loop (if then)

Tue, 10 Feb 2015 03:11:02 GMT

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.

Re: Hierarchical loop (if then)

anbu1984 — Tue, 10 Feb 2015 03:26:10 GMT

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

Re: Hierarchical loop (if then)

Tue, 10 Feb 2015 03:30:07 GMT

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)

Re: Hierarchical loop (if then)

anbu1984 — Tue, 10 Feb 2015 04:14:23 GMT

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

Re: Hierarchical loop (if then)

Tue, 10 Feb 2015 04:34:43 GMT

I want output as follows:

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

Re: Hierarchical loop (if then)

anbu1984 — Tue, 10 Feb 2015 04:40:35 GMT

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

Re: Hierarchical loop (if then)

Tue, 10 Feb 2015 06:08:01 GMT

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

Re: Hierarchical loop (if then)

khalander — Fri, 13 Feb 2015 09:45:45 GMT

Hi Joshmi George,

Thank you it is very useful

Re: Hierarchical loop (if then)

Sat, 21 Feb 2015 04:05:15 GMT

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'