6 Replies Latest reply: Jun 5, 2014 8:42 AM by Marcel Hug RSS

    Hierarchy with cyclic elements

    Anatoly Pyatygo

      Hello, guys.

       

      I have a problem with my BOM hierarchy.

       

      I have source table like this:

       

       

      NodeIDParentID

      225487


      103796225487
      140359103796
      140359225487
      100000
      100001100000
      100002100001

       

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

      Cyclic in hierarchy.png

      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

          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.

            • Hierarchy with cyclic elements
              Anatoly Pyatygo

              Eduardo, thanks.

               

              but...

               

              It's incredible!!!

               

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

               

              Somebody can me explain?

                • Re: Hierarchy with cyclic elements

                  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
                      Anatoly Pyatygo

                      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;