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:
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
;
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
;
