topic Re: Username/Department Roll-up Logic in QlikView

Username/Department Roll-up Logic

johnca — Mon, 01 Aug 2016 15:50:09 GMT

Hello all,

I've been struggling with some logic that will return department number roll-ups to the highest level, in this case the queen bees.

My data is like this...

Username	Title	Department	Manager
Joe	Worker Bee	200	King Bee 1
Mary	Worker Bee	300	King Bee 2
Larry	King Bee 1	450	Queen Bee 1
Mo	King Bee 2	475	Queen Bee 2
Anita	Queen Bee 1	100
Toni	Queen Bee 2	105
El Jeffe	Worker Bee	335	King Bee 2
Shemp	Worker Bee	250	King Bee 1

I need to return data rolled up by Queen like this...

Queen Bee	Departments
Anita	200
Anita	450
Anita	100
Anita	250
Toni	300
Toni	475
Toni	105
Toni	335

Thanks,

John

Re: Username/Department Roll-up Logic

Gysbert_Wassenaar — Mon, 01 Aug 2016 16:14:58 GMT

You're looking for the Hierarchy function. See this blog post: Unbalanced, n-level hierarchies

Re: Username/Department Roll-up Logic

Anonymous — Mon, 01 Aug 2016 16:28:50 GMT

HI John,

PFA the sample app.

Hope this is the requirement...

Re: Username/Department Roll-up Logic

johnca — Wed, 03 Aug 2016 16:32:37 GMT

Thank you Gysbert...will take some time to digest this...am slammed.

Re: Username/Department Roll-up Logic

johnca — Wed, 03 Aug 2016 16:35:29 GMT

Thank you, too Nitin. I applied this logic to my dataset and it didn't turn out as expected. Hope to get back to this but am slammed with other projects.

Re: Username/Department Roll-up Logic

Anonymous — Wed, 03 Aug 2016 17:19:06 GMT

OK No problem,

But as I understood, the output was same as in you mentioned...

What was the issue???

Re: Username/Department Roll-up Logic

johnca — Wed, 03 Aug 2016 17:23:26 GMT

Hi Nitin, my pseudo dataset may be wrong. I like being told the answer, but to understand the hierarchy function I really need to dig into the documents Gysbert pointed me towards.