Hello,
Extract from the help file :
<h1>Hierarchy</h1>
The hierarchy keyword is a prefix to the load and select statements. It is used to transform an adjacent nodes table to an expanded nodes table. Hence, the input table must be an adjacent nodes table. The output of the hierarchy prefix is an expanded nodes table with all the needed additional fields.
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 can still have any number of children. The table may of course contain additional fields describing attributes for the nodes.
In expanded nodes tables 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 or a in a tree structure. More details about adjacent nodes tables and expanded nodes tables can be found in the Reference Manuals.
Normally 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. If so, the output table may have more records than the input table.
All nodes without a parent id or with a parent id not found in the nodeID column 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.
ParentID is the name of the field that contains the node id of the parent node.
NodeName is the name of the field that contains the name of the node.
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. <h2>Example:</h2>
Hierarchy(NodeID, ParentID, NodeName) Load
NodeID,
ParentID,
NodeName,
Attribute
From data.xls (biff, embedded labels, table is [Sheet1$];
Hierarchy Parameters<h1>Hierarchy Parameters</h1><table><tbody><tr><td>
Source Parameters</td><td>
ID Field (NodeID)
The name of the field that contains the node id.
Parent ID Field (ParentID)
The name of the field that contains the node id of the parent node.
Name Field (NodeName)
The name of the field that contains the name of the node. </td></tr><tr><td>
Hierarchy Parameters</td><td>
Parent Name (ParentName)
The name of the new ParentName field. Optional parameter.
Path Name(PathName)
The name of the new Path field, which contains the path from the root to the node. Optional parameter.
Depth Name (Depth)
The string used to name the new Depth field, which contains the depth of the node in the hierarchy. Optional parameter.
Path Source (PathSource)
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.
Path Delimiter (PathDelimiter)
The string used as delimiter in the new Path field, e.g. \. Optional parameter. If omitted '/' will be used. </td></tr><tr><td>
Belongs To Parameters</td><td>
Ancestor ID (AncestorID)
The name of the new ancestor id field, which contains the id of the ancestor node.
Ancestor Name (AncestorName)
The name of the new ancestor field, which contains the name of the ancestor node.
Depth Diff Name (DepthDiff)
The name of the new DepthDiff field, which contains the depth of the node in the hierarchy relative the ancestor node. Optional parameter. </td></tr></tbody></table>
HierarchyBelongsTo<h3>HierarchyBelongsTo</h3>
The hierarchybelongsto keyword is a prefix to the load and select statements. 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 can still have any number of children. The table may of course contain additional fields describing attributes for the nodes. More details about adjacent nodes tables can be found in the Reference Manuals.
The prefix creates a table containing all ancestor-child relations of the hierarchy. The ancestor fields can then be used to select entire trees in the hierarchy. The output table usually contains several records per node.
An additional field containing the depth difference of the nodes can be created.
The syntax is:
HierarchyBelongsTo (NodeID, ParentID, NodeName, AncestorID, AncestorName, [DepthDiff]) (loadstatement | selectstatement)
where
NodeID is the name of the field that contains the node id.
ParentID is the name of the field that contains the node id of the parent node.
NodeName is the name of the field that contains the name of the node.
AncestorID is a string used to name the new ancestor id field, which contains the id of the ancestor node.
AncestorName is a string used to name the new ancestor field, which contains the name of the ancestor node.
DepthDiff is a string used to name the new DepthDiff field, which contains the depth of the node in the hierarchy relative the ancestor node. Optional parameter. If omitted, this field will not be created. <h2>Example:</h2>
HierarchyBelongsTo (NodeID, ParentID, Node, Tree, ParentName) Load
NodeID,
ParentID,
NodeName
From data.xls (biff, embedded labels, table is [Sheet1$];
Hope it's gonna be helpful
Martin
