Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ...
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.
Eduardo, thanks.
but...
It's incredible!!!
How is it work ? I can't understand...
Somebody can me explain?
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
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;
Great!!
Maybe the function Hierarchy needed the values return in order.
I don't know too how after invert the parameters the function works.
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