5 Replies Latest reply: Feb 23, 2015 8:56 AM by Manish Kachhia RSS

    Hierarchy Load

    Hans Müller

      Hi,

       

      how i can build up an hierarchy load of this table?

       

       

      LOAD * INLINE [
      ID, Name
      A001, Tom
      B012, Dave
      C043, Karl
      ]
      ;

       

      The solution must be ...

       

      A001

        |_ B012

                |_C043

       

      I Need help...

       

      Thanks...

        • Re: Hierarchy Load
          Manish Kachhia

          Until and Unless you don't have parent child relationship between field values, you can't create hierarchy !

            • Re: Hierarchy Load
              Hans Müller

              Is it possible with this table??:

               

               

              LOAD * INLINE [
              ID, Name, ID2
              A001, Tom, A012
              A012, Dave, A025
              A025, Karl, A035
              ]
              ;

                • Re: Hierarchy Load
                  Joshmi George

                  Hi Thomas,

                   

                  Try the below code

                   

                  TREE:

                  HierarchyBelongsTo(ID2, ID, Name, 'ancestorid', 'ancestor', 'layer')

                  load ID2,

                  ID,

                  Name

                  resident [InlineTable];

                   

                  Regards,

                  Joshmi

                  • Re: Hierarchy Load
                    Manish Kachhia

                    Still Wrong... I have done little correction.. Use the same to make changes at your end.

                    Kindly use below script...

                     

                    temp:

                    LOAD * INLINE [

                    ID, Name, ID2

                    A001, Tom, A001

                    A002, Dave, A001

                    A003, Karl, A002

                    ];

                     

                     

                    Hierarchy (NodeID, ParentID, Name, , Name, NodePath, '/', NodeDepth)

                    LOAD

                      ID as NodeID,

                      ID2 as ParentID,

                         Name    

                    Resident temp;

                     

                    Now create a list box for NodePath and tick show as treeview from general tab

                • Re: Hierarchy Load
                  Andrew Whitfield

                  Hi Thomas,

                   

                  it's covered pretty well in the QV Designer help, e.g.

                   

                  The hierarchy prefix is used to transform a hierarchy table to a table that is useful in a QlikView data model.

                   

                  It can be put in front of a Load or a Select statement and will use the result of the loading statement as input for a table transformation.

                   

                  The input table must be an adjacent nodes table.

                   

                  Adjacent nodes tables are tables where each record corresponds to a node and has a field that contains a reference to the parent node. In such a table the node is stored on one record only but the node can still have any number of children. The table may of course contain additional fields describing attributes for the nodes.

                   

                  The prefix creates an expanded nodes table, which normally has the same number of records as the input table, but in addition each level in the hierarchy is stored in a separate field. The levels in an expanded nodes table can easily be used e.g. in a pivot table and the path field can be used in a tree structure. More details about adjacent nodes tables and expanded nodes tables can be found in the section Hierarchies the QlikView Reference Manual.

                   

                  Usually the input table has exactly one record per node and in such a case the output table will contain the same number of records. However, sometimes there are nodes with multiple parents, i.e. one node is represented by several records in the input table. If so, the output table may have more records than the input table.

                   

                  All nodes with a parent id not found in the nodeid column (including nodes with missing parent id) will be considered as roots. Also, only nodes with a connection to a root node - direct or indirect - will be loaded, thus avoiding circular references.

                   

                  Additional fields containing the name of the parent node, the path of the node and the depth of the node can be created.

                   

                  The syntax is:

                   

                  Hierarchy (NodeID, ParentID, NodeName, [ParentName], [PathSource], [PathName], [PathDelimiter], [Depth])(loadstatement | selectstatement)

                   

                  where

                   

                  NodeID is the name of the field that contains the node id. This field must exist in the input table.

                   

                  ParentID is the name of the field that contains the node id of the parent node. This field must exist in the input table.

                   

                  NodeName is the name of the field that contains the name of the node. This field must exist in the input table.

                   

                  ParentName is a string used to name the new ParentName field. If omitted, this field will not be created.

                   

                  PathSource is the name of the field that contains the name of the node used to build the node path. Optional parameter. If omitted, NodeName will be used.

                   

                  PathName is a string used to name the new Path field, which contains the path from the root to the node. Optional parameter. If omitted, this field will not be created.

                   

                  PathDelimiter is a string used as delimiter in the new Path field. Optional parameter. If omitted, ‘/’ will be used.

                   

                  Depth is a string used to name the new Depth field, which contains the depth of the node in the hierarchy. Optional parameter. If omitted, this field will not be created.

                   

                  Example:

                   

                  Hierarchy(NodeID, ParentID, NodeName) LOAD

                   

                  NodeID,

                   

                  ParentID,

                   

                  NodeName,

                   

                  Attribute

                   

                  FROM data.xls (biff, embedded labels, table is [Sheet1$];