Hierarchy function - tree view how to

[#1]

Hi

I didn’t understand how to use Hierarchy function.

I want to create a Hierarchy tree view (in order to use it as a filter) by using only the first three

fields: Region, SubRegion and Country.

All the examples I saw in the forum used the Hierarchy function when they have two rows: ID and Parent.
and didn’t refer the when you have horizontal Hierarchy (when each column is node).

I found another way to do it but the problem is when the sub node is expanded I get nothing only when

  It Collapsed .- see attached

thank you

[hic]

The hierarchy prefix is designed to resolve an adjacent nodes table, i.e. a table where each record corresponds to a node and has a reference to its parent. You do not have an adjacent nodes table. But your solution with three Load statements - one for each level - to create the path for the tree-view list box is the proper way to load a horizontal hierarchy. See more on Hierarchies

The second question is about the behaviour of the tree-view list box: If you make a selection on a collapsed node, you select the entire sub-tree. But if you make a selection on an expanded node, you will select this node only. And that is how the tree view list box is designed.

If you want a selection on  a node to link to the entire sub-tree, you should build this logic into the data model - you should create a Tree table. See more on Unbalanced hierarchies

HIC

[#3]

Hi

i wrote this question after i reading your blog  and the Hierarchies.pdf

and download the Winedistricts  files

i probably missing something  because i still  don't understand , how to take the horizontal hierarchy and

create Tree table.

if you can just give me a lead regarding my files maybe i would understand better .

thank you

[hic · Accepted solution]

When you use the Trees table, you need to link this with your data using a HierarchyBelongsTo that creates all combinations between an ancestor and a node. Hence, you need your data as an Adjacent Nodes table.

If you load your data using the following lines, you will get an adjacent nodes table that you can use in subsequent Loads:

AdjacentNodes:

LOAD distinct

  'Region:' & Region_Name as NodeID, Null() as ParentID

  FROM UserDim.xls (biff, embedded labels, table is Sheet2$);

LOAD distinct

  'SubRegion:' & SubRegion_Name as NodeID, 'Region:' & Region_Name as ParentID

  FROM UserDim.xls (biff, embedded labels, table is Sheet2$);

LOAD distinct

  'Country:' & Country_Name as NodeID, 'SubRegion:' & SubRegion_Name as ParentID

  FROM UserDim.xls (biff, embedded labels, table is Sheet2$);

LOAD distinct EndUser_ID as NodeID,

  'Country:' & Country_Name as ParentID

  FROM UserDim.xls (biff, embedded labels, table is Sheet2$);

HIC

[#5]

thank you

[#6]

hi

one more question :

i am using GetFieldSelections function to see the string of the node.

sometimes it return string like this :NORTH_AMERICA>CANADA, NORTH_AMERICA>CANADA>CANADA

and for some node it return :   7 of 215

why ???

[arjunkrishnan]

Use This On ...Better Than Other

TEST1:

LOAD Distinct

    '0' as ParentID,

Region_Name AS Value,

AutoNumber('P-'&Region_Name) as [S-num] FROM UserDim.xls (biff, embedded labels, table is Sheet2$);

;

TEST2:

LOAD Distinct

AutoNumber('P-'&Region_Name) as ParentID,

Region_Name AS Value,

  AutoNumber('C-'&Region_Name) as [S-num] FROM UserDim.xls (biff, embedded labels, table is Sheet2$);

TEST3:

LOAD Distinct

AutoNumber('C-'&Region_Name) as ParentID,

[MIS Line Item] AS Value,

  AutoNumber('C1-'&Region_Name) as [S-num] FROM UserDim.xls (biff, embedded labels, table is Sheet2$);

[hic]

It does that when there are more than 6 possible values. I would instead use

   Concat(distinct NodeName, ',')

which returns a list of all node names, or

   Only(NodeName)

which returns the node name if there is only one possible node name, otherwise NULL.

HIC