topic Re: Build a Hierarchy from a field separated by / in QlikView

Build a Hierarchy from a field separated by /

Tue, 18 Oct 2016 02:46:45 GMT

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

Re: Build a Hierarchy from a field separated by /

Gysbert_Wassenaar — Tue, 18 Oct 2016 08:27:43 GMT

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.

Re: Build a Hierarchy from a field separated by /

Tue, 18 Oct 2016 16:22:48 GMT

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.

Re: Build a Hierarchy from a field separated by /

Gysbert_Wassenaar — Wed, 19 Oct 2016 07:30:38 GMT

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.

Re: Build a Hierarchy from a field separated by /

Wed, 19 Oct 2016 20:38:37 GMT

gwassenaar‌ thanks for the help that did the trick