Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
thank you
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 ???
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$);
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