Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'd like to make a hierarchy tree out of my database.
Here is an example of my data:
Level GroupID GroupName IdGroupparent
0 22550 Test1
1 22551 Test2 22550
1 22552 Test3 22550
1 22553 Test4 22550
2 22554 Test5 22551
2 22555 Test6 22551
3 22556 Test7 22554
4 22557 Test8 22556
Basically I'd like to build a view like the following suggested in this post: Hierarchy List
However they're using the 'Path' to build the table below. I'd like to automatically be able to organize my Groups from top to bottom.
I've got a maximum of 6 levels.
Result would look like this:
Test1
Test2
Test5
Test7
Test8
Test6
Test3
Test4
Sorry I'm a bit new to Qlikview, if you can provide me some help that would be great.
Thanking you in advance,
Cédric
You should use a Hierarchy load to do this:
Hierarchy (GroupID, IDGroupparent, GroupName, ParentName, GroupName, Path, '/', Depth)
Load
GroupID,
GroupName,
IDGroupparent
From ...
Then you can use the Path field in a list box. See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/11/unbalanced-n-level-hierarchies
HIC
Hi Henric,
Thanks for your quick reply, I don't have any fields such as 'Path' so I can't build the tree.
In your example 'NodePath' is used to build the tree view list box. What can I do to overcome this?
I have done the following:
HIERARCHY([G_ID), [IdGroupe_Parent], [G_Nom]) LOAD
[G_ID],
[IdGroupe_Parent],
[G_Nom]
Resident Database;
Thanks,
Cédric
Cédric,
I wrote a document on that subject:
It may help you. Once you have created your hierarchy, you must get data at upper levels (when choosing Level1, the user will want some data). The doc explains that also (in english).
Fabrice
Bonne lecture
If you have load script like the following, you can use the [Tree] field in a list box:
Hierarchy:
HIERARCHY([G_ID],[IdGroupe_Parent],[G_Nom],,[G_Nom],[Tree],'/',[Depth]) LOAD
[G_ID],
[IdGroupe_Parent],
[G_Nom];
LOAD * INLINE [
Level,G_ID,G_Nom,IdGroupe_Parent
0,22550,Test1,
1,22551,Test2,22550
1,22552,Test3,22550
1,22553,Test4,22550
2,22554,Test5,22551
2,22555,Test6,22551
3,22556,Test7,22554
4,22557,Test8,22556
];
I've also attached a working example.
But you don't need to have the Path field - it is created when you make a Hierarchy Load.
HIC
Hi,
Thank you for your replies, I think I am getting closer!
How to query my database instead of an xls file?
TEST: |
HIERARCHY (G_ID, IdGroupe_Parent,G_Nom, [HIERARCHIE], '|', 'HIERARCHY DEPTH')
LOAD G_ID, IdGroupe_Parent, G_Nom
FROM mydatabase.dbo
but it's not working, it keeps looking for a file on my drive instead of SQL
Thanks again!
Then another document may be helpful:
The way of loading data from SQL differ a little from a file.
Fabrice
TEST: |
HIERARCHY (G_ID, IdGroupe_Parent,G_Nom, [HIERARCHIE], '|', [HIERARCHY DEPTH])
SQL SELECT G_ID, IdGroupe_Parent, G_Nom
FROM mydatabase.dbo;
EDIT: You also need to make sure you're connected to the database through an ODBC or OLE DB connection.
I can now see my newly created Table called "HIERARCHY" and "HIERARCHY DEPHT" however is not doing the hierarchy thing ... like G_Nom with the "|" in between groups. Don't understand why.
I can only see my group without seeing its hierarchy
Hierarchy:
HIERARCHY (G_ID, IdGroupe_Parent,G_Nom, [HIERARCHY], '|', [HIERARCHY DEPTH])
SQL SELECT G_ID, IdGroupe_Parent,G_Nom
FROM ActiveDirectory.dbo."AD_Comptes_ArbActive_V3";
LOAD G_ID, IdGroupe_Parent,G_Nom