Hierarchy with cyclic elements

[sparur]

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 ...

[#2]

Hi,

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

HIERARCHY(NodeID, ParentID, NodeName)

TO

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.

[sparur]

Eduardo, thanks.

but...

It's incredible!!!

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

Somebody can me explain?

[#4]

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

[sparur]

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;

[#6]

Great!!

Maybe the function Hierarchy needed the values return in order.

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

[hugmarcel]

Hi

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