topic Re: Hierarchy with cyclic elements in QlikView

Hierarchy with cyclic elements

sparur — Wed, 22 Jun 2011 12:44:09 GMT

Hello, guys.

I have a problem with my BOM hierarchy.

I have source table like this:

NodeID	ParentID
225487
103796	225487
140359	103796
140359	225487
100000
100001	100000
100002	100001

so, if we will draw hierarchy scheme, we will get like this:

on the left picture we see a simple (good) situation.

but on the right picture, we see the cyclic links in elements of hierarchy and Qlikview can not managed it by the HIERARCHY statement.

QlikView doesn't create a link, which I crossed out on the right picture.

But I need it. How I can create this link in the hierarchy?

I want to get something like this:

225487 -> 103796 -> 140359

and

225487 -> 140359

How can I achive it?

I attached small qvw-example ...

Hierarchy with cyclic elements

Wed, 22 Jun 2011 14:58:46 GMT

Hi,

I don't know what happened, but if you change the sintaxe of hierarchy:

HIERARCHY(NodeID, ParentID, NodeName)

HIERARCHY(ParentID, NodeID, NodeName)

we have the result that you want.

Why??

I don't know.. i tried with in_line table and result are OK, but with using joins are problemn.

Hierarchy with cyclic elements

sparur — Wed, 22 Jun 2011 15:54:58 GMT

Eduardo, thanks.

but...

It's incredible!!!

How is it work ? I can't understand...

Somebody can me explain?

Re: Hierarchy with cyclic elements

Wed, 22 Jun 2011 17:57:58 GMT

Hi,

I put the result of joins tables in "IN LINE" and the function HIERARCHY executed OK.

tab:
LOAD * INLINE [
    NodeID_, ParentID_, NodeName_
    225487, , B0
    103796, 225487, B1
    140359, 225487, B2
    140359, 103796, B2
];

Extended:
HIERARCHY(NodeID_, ParentID_, NodeName_)
LOAD
NodeID_,
ParentID_, // as Parent,
NodeName_
RESIDENT tab;

DROP TABLE tab;

When we used joins in second plan the system generate other table (Cartesian Product) the return the values. I believe that can be a bug in this function when exists null values in the table created.

So "IN LINE" just return 4 rows, and using the joins I think that returns different results.

I save the two results in qvd because then returns just four rows in final table, but the files created are different.

I think that is a bug in the function hierarchy

Re: Hierarchy with cyclic elements

sparur — Wed, 22 Jun 2011 19:12:44 GMT

Eduardo, sorry but I don't understand your explanation,

but I played with order of lines in source data and found one strange behaviour...

If we have such order of lines:

NodeID, ParentID, NodeName

100000, , A0

225487, , B0

103796, 225487, B1

140359, 225487, B2

140359, 103796, B2

100001, 100000, A1

100002, 100001, A2

HIERARCHY function is worked OK.

but if we change order like this:

NodeID, ParentID, NodeName

100000, , A0

225487, , B0

140359, 225487, B2

103796, 225487, B1

140359, 103796, B2

100001, 100000, A1

100002, 100001, A2

or like this:

NodeID, ParentID, NodeName

100000, , A0

225487, , B0

140359, 225487, B2

140359, 103796, B2

103796, 225487, B1

100001, 100000, A1

100002, 100001, A2

HIERARCHY function do NOT work after this changes

I can give only one assumption- source lines must be sorted by NodeName.

but I can't understand How I can do it if I don't have NodeName and I use NodeID instead of NodeName, like this:

Extended:

HIERARCHY(NodeID_, ParentID_, NodeName_)

LOAD

NodeID as NodeID_,

ParentID as ParentID_,

NodeID as NodeName_

RESIDENT tmp;

Re: Hierarchy with cyclic elements

Wed, 22 Jun 2011 19:33:35 GMT

Great!!

Maybe the function Hierarchy needed the values return in order.

I don't know too how after invert the parameters the function works.

Re: Hierarchy with cyclic elements

hugmarcel — Thu, 05 Jun 2014 12:42:51 GMT

I made the experience that HIERARCHY command results depend on sort order of table records.

However, I do not know why this is the case.

marcel