topic Re: How to create a listbox hierarchy from a table with many columns? in QlikView

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

QlikToFindOut — Tue, 29 May 2018 21:37:40 GMT

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.

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

ogautier62 — Wed, 30 May 2018 10:19:07 GMT

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

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

QlikToFindOut — Wed, 30 May 2018 13:48:03 GMT

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.

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

ogautier62 — Wed, 30 May 2018 14:40:48 GMT

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

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

QlikToFindOut — Wed, 30 May 2018 15:09:04 GMT

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;

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

MarcoWedel — Wed, 30 May 2018 21:09:19 GMT

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

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

QlikToFindOut — Thu, 31 May 2018 14:24:28 GMT

This works out incredibly well, thank you!

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

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

MarcoWedel — Thu, 31 May 2018 20:35:36 GMT

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

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

QlikToFindOut — Thu, 31 May 2018 21:05:34 GMT

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.