topic Reproducing an Organization Chart in QlikView

Reproducing an Organization Chart

Wed, 18 Apr 2012 17:47:51 GMT

I've come across a rather wierd problem that I'm not sure I can describe adequately, so please bear with me.

I'm trying to build a table structure that will essentially contain my company's org chart. At the end of the day, it should look something like this:

Level 1 Level 2 Level n

CEO <Null> <Null>

CEO SVP1 <Null>

CEO SVP1 VP 1

CEO SVP1 VP 2

CEO SVP2 <Null>

CEO SVP3 <Null>

...

So, to put that mess into language: Every Employee in the company should have a row where they are the leaf node (IE: a Null to the right of them in the above table). This thing goes about 10 levels deep, but that's not really the issue. The source data is a table of every resource in the company, with a unique Resource ID for the employee and a Supervisor ID for their immediate boss. I've managed to build a table with the key being the Supervisor ID of the resource to the Resource ID of somone one level above them.

This works fine, but managers (anyone with a person below them in the hierarchy) don't have their own row. As a result, the table above, looks like:

Level 1 Level 2 Level n

CEO SVP1 VP 1

CEO SVP1 VP 2

CEO SVP2 <Null>

CEO SVP3 <Null>

...

This kinda breaks my app.

I've gone through the table config and have turned on/off all the settings related to suppressing Nulls (than I can find, anyway) and I think I've exhausted all of those options. Maybe there's something I'm missing.

Anyone got any ideas how to resolve this?

Re: Reproducing an Organization Chart

Wed, 18 Apr 2012 17:53:42 GMT

I am assuming that currently your first table is the top and it flows down? Have you tried reversing your table structure so the lowest table would be CEO?

Also what is the expression that you are using and which table do they reside in?

Reproducing an Organization Chart

Wed, 18 Apr 2012 18:41:43 GMT

Thanx for responding, Marc.

The Source Table is just a row by row listing of every employee in the company. So there's name, id, supervisor id, department, etc. on one row, and there's about 18K rows. The order of the rows appears to be pretty much random. In other words, I don't have the luxury of saying, "Row 1 is the CEO and rows 2-n are his direct reports."

The table I'm trying to build is intended to be a lookup. I want to be able to find a resource ID in a defined column and be able to find out what EVP, SVP, etc. they fall under.

I don't know if I explained that very well. Let me try to show you.

Level 1 Level 2 Level 3 ... Level N Resource ID

CEO <null> <null> <null> CEO

...

CEO SVP1 <Null> <null> SVP1

...

CEO SVP1 Nick's Boss Nick Nick

The order of the rows themselves are irrelevant, of course. The ultimate objective is to be able to determine the level 2 and 3 bosses of anyone who logs a service desk ticket, for SLA purposes. That system gives me the name and ID of who logged the ticket, but not much info on their hierarchy. I have an algorith that figures out the leaf node and replicates it in the Resource ID column. The lack of a leaf node for a manager, however, is what's killing me.

Oh, we're talking internal IT here, so there won't be any tickets logged by customers or anything like that.

I could just build this externally in Java or something, but I'm new to QV and wanted to see how far I can push the platform. I've just gotten so close that I thought this would work, but then I hit this wall.

I have also discovered the Hierarchy command in QV and am investigating whether that's what I should be working with.

marcsliving wrote:
Also what is the expression that you are using and which table do they reside in?

Well, my code snippet looks like:

Level_1:

LOAD
    [Resource ID] as [L1 Resource ID],
    [First name] as [L1 First name],
    [Last name] as [L1 Last name],
    [Resource Type] as [L1 Resource Type],
    [Contractor Role] as [L1 Contractor Role],
    Title as [L1 Title],
    [Phone number] as [L1 Phone number],
    Email as [L1 Email],
    [Dept ID] as [L1 Dept ID],
    [Dept Name] as [L1 Dept Name],
    [Supervisor ID] as [L1 Supervisor ID],
    [Supervisor Name] as [L1 Supervisor Name],
    [IT Area] as [L1 IT Area],
    [Dept VP] as [L1 Dept VP],
    [Cost Center Owner] as [L1 Cost Center Owner]

Resident
    Source

where
    [Resource ID] = 439545
;

439545 being the CEO's ID

and then,

Level_2:

LOAD
    [Resource ID] as [L2 Resource ID],
    [First name] as [L2 First name],
    [Last name] as [L2 Last name],
    [Resource Type] as [L2 Resource Type],
    [Contractor Role] as [L2 Contractor Role],
    Title as [L2 Title],
    [Phone number] as [L2 Phone number],
    Email as [L2 Email],
    [Dept ID] as [L2 Dept ID],
    [Dept Name] as [L2 Dept Name],
    [Supervisor ID] as [L2 Supervisor ID],
    [Supervisor ID] as [L1 Resource ID],
    [Supervisor Name] as [L2 Supervisor Name],
    [IT Area] as [L2 IT Area],
    [Dept VP] as [L2 Dept VP],
    [Cost Center Owner] as [L2 Cost Center Owner]

Resident
    Source

where
[Resource ID] <> 'C0708'
;

C0708 being some sort of garbage in the feed I'm getting from HR.

Everything else, levels 3-10, follows the above model.

Thanx for reading, and if you have any ideas, I'm keen to hear 'em. For my part, I'm gonna go hit the manual and read up on this hierarchy command.

Level_1:

Reproducing an Organization Chart

Wed, 18 Apr 2012 19:00:18 GMT

I have never tried doing something this deep. I know for our financial we have account categories that each record shows the current category, then its parent category. I needed to show the flow in a pivot so I just attached the table a few times and linked it the way you are and that seemed to work.

Something you could try in each table is this:

Note sure if it will work or what the results will be:

if(isnull([Supervisor ID]) or [Supervisor ID]='', [Resource ID],[Supervisor ID])

This should tell it that if there is no supervisor then the current employee is their own supervisor.

Reproducing an Organization Chart

Wed, 18 Apr 2012 20:27:54 GMT

Looks like my answer was to read the manual.

HIERARCHY ([Resource ID], [Supervisor ID], [Name], 'Boss', 'Title', 'Path', '|', 'Depth')

LOAD

[Resource ID],

[First name],

[Last name],

[Last name] & ', ' & [First name] as [Name],

[Resource Type],

[Contractor Role],

Title,

[Phone number],

Email,

[Dept ID],

[Dept Name],

[Supervisor ID],

[Supervisor Name],

[IT Area],

[Dept VP],

[Cost Center Owner],

Executroid

FROM

(biff, embedded labels, table is Sheet1$);

...and it's all gravy from there.

Anyhow, thanx for your help, Marc!