Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Can you post the script/expr which you tried ( I am using Personal edition )
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)
With Ref as 2 for Entry No 6, are you not getting doc as B? Or are you expecting different output?
I want output as follows:
Entry No. | Reference No. | Document No. |
5 | 2 | BBB |
6 | 2 | BBB |
I am getting BBB as Doc with your script and expr. Check the attached app
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
Hi Joshmi George,
Thank you it is very useful
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'