Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I was wondering if there is a way to asign parent nodes in the loading script if you know the loading order.
For example,
My data is organized in rows, that have the same field names and everything.
The only thing that diffirenciates them is a level column, 5 to 0.
The hiest hierarchy are 5.
The thing is, the parent of each row is the next value that has a hier hierarchy lvl.
For example:
ID | ParentID | Name | Level |
---|---|---|---|
1 | 3 | Row1 | 0 |
2 | 3 | Row2 | 0 |
3 | 10 | Row3 | 4 |
4 | 6 | Row4 | 0 |
5 | 6 | Row5 | 0 |
6 | 7 | Row6 | 1 |
7 | 8 | Row7 | 2 |
8 | 9 | Row8 | 3 |
9 | 10 | Row9 | 4 |
10 | 0 | Row10 | 5 |
In here you can see that each node has the parentid of the next node with higher hierarchy.
I try using variables that would change their value but you cant do that on the load script.
So i was wondering if there is a way to do this with a function i dont know about or a trick some of you expierienced guys might know.
Any help is appriciated.
Thanks,
KR
P.S. Sry for my poor english
Maybe like this (assuming a max level of 5 as you stated above):
RAW:
LOAD ID,
ParentID as Check,
Name,
Level
FROM
[https://community.qlik.com/thread/172577]
(html, codepage is 1252, embedded labels, table is @1);
T1:
LOAD *,
if(Level = 1, ID, Peek(Next1)) as Next1,
if(Level = 2, ID, Peek(Next2)) as Next2,
if(Level = 3, ID, Peek(Next3)) as Next3,
if(Level = 4, ID, Peek(Next4)) as Next4,
if(Level = 5, ID, Peek(Next5)) as Next5
RESIDENT RAW
ORDER BY ID desc;
DROP TABLE RAW;
RESULT:
LOAD recno() as Recno,
ID,
Name,
Level,
rangesum(rangemin(if(Level<1,Next1),if(Level <2,Next2),if(Level<3,Next3),if(Level<4,Next4),if(Level<5,Next5))) as ParentID,
Next1,Next2,Next3,Next4,Next5,
Check
RESIDENT T1
ORDER BY ID asc ;
DROP TABLE T1;
you can use the hierarchy function for this
Maybe like this (assuming a max level of 5 as you stated above):
RAW:
LOAD ID,
ParentID as Check,
Name,
Level
FROM
[https://community.qlik.com/thread/172577]
(html, codepage is 1252, embedded labels, table is @1);
T1:
LOAD *,
if(Level = 1, ID, Peek(Next1)) as Next1,
if(Level = 2, ID, Peek(Next2)) as Next2,
if(Level = 3, ID, Peek(Next3)) as Next3,
if(Level = 4, ID, Peek(Next4)) as Next4,
if(Level = 5, ID, Peek(Next5)) as Next5
RESIDENT RAW
ORDER BY ID desc;
DROP TABLE RAW;
RESULT:
LOAD recno() as Recno,
ID,
Name,
Level,
rangesum(rangemin(if(Level<1,Next1),if(Level <2,Next2),if(Level<3,Next3),if(Level<4,Next4),if(Level<5,Next5))) as ParentID,
Next1,Next2,Next3,Next4,Next5,
Check
RESIDENT T1
ORDER BY ID asc ;
DROP TABLE T1;
You can use hirarchy like following example
Test:
HIERARCHY(CHILD_ID, PARENT_ID, [HQ], [Parent Name], [HQ], [Path Name], '/')
LOAD Hqid AS [HQ Code],
Hqname AS [HQ],
GeoLevelName AS [Level Name],
CHILD_ID,
PARENT_ID
FROM
[..\Qvds\DIM_GeoHQMaster.qvd]
(qvd);
Hi,
Try using the Hierarchy concepts in Qlikview, check below link hope it helps you.
Regards,
jagan.
I think i didnt explianed myself apprpiate, the column of parentID is the colum i need to create on the load.
I think i didnt explianed myself apprpiate, the column of parentID is the colum i need to create on the load.
That's what I assumed. In my example above, I only loaded the existing column as Check to validate the results. I calculate ParentID only using ID and Level.
Thanks alot, this solved my problem completly, ur a qv wizard.
thanks again!