Discussion Board for collaboration on QlikView Scripting.
I have a problem with my BOM hierarchy.
I have source table like this:
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
225487 -> 140359
How can I achive it?
I attached small qvw-example ...
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.
I don't know.. i tried with in_line table and result are OK, but with using joins are problemn.
How is it work ? I can't understand...
Somebody can me explain?
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_)LOADNodeID_,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:
or like this:
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:
HIERARCHY(NodeID_, ParentID_, NodeName_)
NodeID as NodeID_,
ParentID as ParentID_,
NodeID as NodeName_
Maybe the function Hierarchy needed the values return in order.
I don't know too how after invert the parameters the function works.
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.