Is there a way to remove node IDs in a hierarchy that do not have a parent or child nodes?

[Jnkansayeboah]

I have created a hierarchy in Qlik sense using the general syntax:

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

Is there a way I can remove Node IDs within my hierarchy that do not have any parent IDs or Child IDs. I believe the term for these nodes are orphans? Any help would be appreciated. Thanks!

[rubenmarin]

Hi, I don't know if ther is a better way but I'm thinking in a filter before creating the hierarchy, to mount hte hierarchy you need a list of pairs Node-parent. 

From the nodes without parent you want to remove those that doesn't have any children nodes, and these are the nodes that no other node has them as parents, so first do a check list with those that has been used as parent from other nodes:

chkHasChildren:
LOAD ParentNode as chkHasChildren
From/Resident NodeList;

And clean the node list removing those that doesnt has any parent and no other node has set them as parent node:

tmpNodeList:
LOAD
  NodeID,
  ParentID
Resident/From NodeList
Where ParentID is not null
  or Exist('chkHasChilds', NodeID);

[GaryGiles]

Try this in your load script after the Hierarchy table is loaded.  Replace NodeID and ParentID with the appropriate column names and HierarchDimension with the appropriate table name.

NodesParents:

load ParentID AS NodeID,

               count(distinct NodeID) as NUMBER_OF_PARENTS

Resident HierarchyDimension

Group by ParentID;

NodesChildren:

load NodeID,

     count(distinct ParentID) AS NUMBER_OF_CHILDREN

Resident HierarchyDimension

Group by NodeID;

NodesToKeep:

load NodeID

Resident NodesChildren

where NUMBER_OF_CHILDREN >0;

Join (NodesToKeep)

Load NodeID

Resident NodesParents

where NUMBER_OF_PARENTS > 0;

Inner Join (HierarchyDimension)

Load NodeID

Resident NodesToKeep;

drop tables NodesParents, NodesChildren, NodesToKeep;