topic Asigning Parent Nodes on Load in QlikView

Asigning Parent Nodes on Load

Tue, 14 Jul 2015 23:10:29 GMT

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,

P.S. Sry for my poor english

Re: Asigning Parent Nodes on Load

ramoncova06 — Wed, 15 Jul 2015 00:31:21 GMT

you can use the hierarchy function for this

Re: Asigning Parent Nodes on Load

swuehl — Wed, 15 Jul 2015 00:57:30 GMT

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;

Re: Asigning Parent Nodes on Load

vikasmahajan — Wed, 15 Jul 2015 03:38:05 GMT

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);

Re: Asigning Parent Nodes on Load

jagan — Wed, 15 Jul 2015 03:53:35 GMT

Hi,

Try using the Hierarchy concepts in Qlikview, check below link hope it helps you.

Hierarchies

Regards,

jagan.

Re: Asigning Parent Nodes on Load

Wed, 15 Jul 2015 17:45:13 GMT

I think i didnt explianed myself apprpiate, the column of parentID is the colum i need to create on the load.

Re: Asigning Parent Nodes on Load

swuehl — Wed, 15 Jul 2015 17:57:37 GMT

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.

Re: Asigning Parent Nodes on Load

Wed, 15 Jul 2015 18:33:08 GMT

Thanks alot, this solved my problem completly, ur a qv wizard.

thanks again!