Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Build a Hierarchy from a field separated by /

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 StructureResourceNameTime PeriodHrsWorked
Bob/Jane/JamesJeanne132
Bob/Jane/JamesJeanne240
Bob/EarlyJeanne320
Bob/CandanceDebbie132

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
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.


talk is cheap, supply exceeds demand
Not applicable
Author

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

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.


talk is cheap, supply exceeds demand
Not applicable
Author

gwassenaar‌ thanks for the help that did the trick