Build a Hierarchy from a field separated by /

[#1]

Hello all,

I am fairly New to QlikView and have run into a headscratcher.  I need to be able to tell how many hours a resource has worked for their manager. I have a table that has their org structure, resource name, time period and hours worked.

Example

Org Structure	ResourceName	Time Period	HrsWorked
Bob/Jane/James	Jeanne	1	32
Bob/Jane/James	Jeanne	2	40
Bob/Early	Jeanne	3	20
Bob/Candance	Debbie	1	32

I need to be able to create a drill down from Bob down to each Resource

example

All resources that work for BOB have worked 124

All Resources that work for Jane have worked 72

All Resources that work for James have worked 72

All Resouces that work for Early have worked 20

All Resources that work for Candance have worked for 32

The problem I am running into is what to do with Jeanne since she has worked for two managers I need to show those two different org structures. If I key off Jeanne than James and early both get 92 hrs, I think I have to split the org structure into a resource manager table but I am stuck any Ideas?

Thanks

[Gysbert_Wassenaar]

Perhaps like this:

Data:

LOAD

  *,

  SubField([Org Structure], '/') as ResourceManager

INLINE [

    Org Structure, ResourceName, Time Period, HrsWorked

    Bob/Jane/James, Jeanne, 1, 32

    Bob/Jane/James, Jeanne, 2, 40

    Bob/Early, Jeanne, 3, 20

    Bob/Candance, Debbie, 1, 32

];

You can then use ResourceManager as dimension and sum(HrsWorked) as expression.

[#3]

gwassenaar,

Thanks for the quick reply that works for me, as a follow up how would i build it as a bar chart showing that bob has 124 hrs

Clicking bob would show Candance 32 hrs, Early 20, and Jane 72, Clicking Jane shows James as 72 hrs.

[Gysbert_Wassenaar · Accepted solution]

Try this then:

Data:

LOAD

  *,

  SubField([Org Structure], '/', 1) as ResMan_Level1,

  SubField([Org Structure], '/', 2) as ResMan_Level2,

  SubField([Org Structure], '/', 3) as ResMan_Level3

INLINE [

    Org Structure, ResourceName, Time Period, HrsWorked

    Bob/Jane/James, Jeanne, 1, 32

    Bob/Jane/James, Jeanne, 2, 40

    Bob/Early, Jeanne, 3, 20

    Bob/Candance, Debbie, 1, 32

];

Create a drill down group with ResMan_Level1, ResMan_Level2 and ResMan_Level3 as dimensions. Then use the drill down group as chart dimension.

[#5]

gwassenaar‌ thanks for the help that did the trick