How to create a listbox hierarchy from a table with many columns?

[QlikToFindOut]

Hello,

I am having trouble creating a listbox hierarchy in which my table consist of many columns/levels. For example, this is how my table looks like:

Level 1	Level 2	Level 3	Level 4
Company	Division 1	Business 1	Line of Business 1
Company	Division 1	Business 1	Line of Business 2
Company	Division 2	Business 3	Line of Business 3

And so forth.

I am trying to create a hierarchy such that the following would be created:

Company

     Division 1

          Business 1

               Line of Business 1

               Line of Business 2

          Business 2

               Line of Business 3

     Division 2

I tried the following:

HierarchyTable:
LOAD [Level 1] & '/' & [Level 2] & '/' & [Level 3] & '/' & [Level 4] AS TreePathView
Resident AltDim;

to no success. It looked like it initially worked but only the lowest level was selectable.

I then tried:

HierarchyTable:
LOAD [Level 1],
     [Level 1] as TreePathView
Resident AltDim;
Concatenate
LOAD [Level 1], [Level 2],
     [Level 1] & '/' & [Level 2] AS TreePathView
Resident AltDim;
...

and so forth.

That was an unsuccessful attempt as well because while it also looked like it initially worked, the selections in the hierarchy listbox did not associate itself with the fields.

That's where I'm currently at and would love any advice whatsoever.

Thanks.

[ogautier62]

Hi,

I think you can use your table in sheets/graph with a group :

create a hierachy group with level 1 to 4 in sheet

regards

[QlikToFindOut]

This sort of works, but doesn't create the desired tree view look. Is there a way to convert it into that?

Checking the "Show as TreeView" in the listbox didn't work.

[ogautier62]

take a look at key word Hierarchy :

Hierarchy (IDNoeud, IDParent, NomNoeud, [NomParent], [SourceChemin],

[NomChemin], [DélimiteurChemin], [Profondeur])(instructionload |

instructionselect)

make a table with ID, ParentID, NodeName

as you do for each level

and convert then into a hierarchy with :

hierarchy(ID, ParentID, NodeName,,,NodePath,'/')

load ID,ParentID, ID as NodeName

then level 1 to 4 will be created and path too,

and show as treeview should work

[QlikToFindOut]

Won't this not work properly? It will create a Cartesian product.

When I tried this earlier, it created:

Company

    Division 1

          Business 1

              Line of Business 1

              Line of Business 2

              Line of Business 3

          Business 2

              Line of Business 1

              Line of Business 2

              Line of Business 3

    Division 2

          Business 1

              Line of Business 1

              Line of Business 2

              Line of Business 3

          Business 2

              Line of Business 1

              Line of Business 2

              Line of Business 3

I did something like this:

HierarchyTreeView:
LOAD Distinct 1 AS NodeID, NULL() AS ParentID, Corporate AS NodeName RESIDENT AltDim;
LOAD Distinct 2 AS NodeID, 1 AS ParentID, Division AS NodeName RESIDENT AltDim;
LOAD Distinct 3 AS NodeID, 2 AS ParentID, [Business Unit] AS NodeName RESIDENT AltDim;
LOAD Distinct 4 AS NodeID, 3 AS ParentID, [Line of Business] AS NodeName RESIDENT AltDim;

[MarcoWedel · Accepted solution]

Hi,

adapting to your data the example already proposed here:

How to create hierarchy levels in qlikview

create a list box as TreeView having only complete dinamic path of a document

Tree View list box

one solution might be:

HierarchyTable:
LOAD [Level 1] as Company,
    [Level 2] as Division,
    [Level 3] as Business,
    [Level 4] as [Line of Business],
    [Level 1] & '/' & [Level 2] & '/' & [Level 3] & '/' & [Level 4] AS TreePath
FROM [https://community.qlik.com/thread/303117] (html, codepage is 1252, embedded labels, table is @1);

TreePath:
LOAD TreePath, 
    Left(TreePath,Index(TreePath&'/','/',IterNo())-1) as TreePathView 
Resident HierarchyTable 
While IterNo()<=SubStringCount(TreePath,'/')+1; 

hope this helps

regards

Marco

[QlikToFindOut]

This works out incredibly well, thank you!

Is there anyway to get around the fact that every selection grays out after a selection?

[MarcoWedel]

you mean you would not expect the excluded values to show as grey?

[QlikToFindOut]

Something along those lines.

Once a selection has been made, every other value is grey. I at least expected the parent and children of that particular selection would be white.