Multiple Hierarchy Levels with Parents Including Children Values

[bashar_f]

Hi, been pulling my hair over this for a week now, please help 😄

I have two tables, Departments and Projects.

Projects looks like this

Project ID	Department ID
001	240
002	150
003	550
004	240

Departments looks like this

Department ID	Parent ID	Department Type
240	150	General Department
550	240	Base Department
150	Null	Sector

Some projects may be directly connected to a department through the department ID in the projects table, but they could also be connected to another department through the parent Id of its department in the department table. So when I count how many projects each department has, I must take into consideration the projects directly connected to it, and its children, and I need to do a drill down process as well.

Using the above example it will look something like this.

Department ID	# of Projects
240	3
550	1
150	4

If I'm going to drill down through Department Type, it would be Sectors>General Departments>Base Departments, and the number of projects for each should be the same as in the measure table above.

I've tried using the hierarchy function, but because projects are directly connected to departments on different levels, you'd get general departments in the base departments levels, and sectors in the general departments level and so on.

I've attached an excel with the tables above. I'd really appreciate some help on this.

[jwjackso · Accepted solution]

To get the count of project by department, you would use the HierarchyBelongsTo  function.  using the data below:

Projects:
Load * Inline [
DepartmentID,ProjectID
240,'001'
150,'002'
550,'003'
240,'005'
];

Departments:
Load * Inline [
DepartmentID,ParentID,DepartmentType
240,150,'General Department'
550,240,'Base Department'
150,,'Sector'
];

DepartmentBelongsToHierarchy:
HierarchyBelongsTo(DepartmentID,ParentID,DepartmentType,AncestorID,AncestorName,DepthDiff)
Load DepartmentID,
ParentID,
DepartmentType
Resident Departments;

You would use the Hierarchy  function to create a drill down pivot table.

[Taoufiq_Zarra]

@bashar_f 

Another approach, a long but fun one.

For the hair! It'll grow out, don't worry 😀

Departments:

Hierarchy([Department ID], [Parent ID],TmpID)

LOAD [Department ID] as TmpID,* INLINE [
    Department ID, Parent ID, Department Type
    240, 150, General Department
    550, 240, Base Department
    150, Null, Sector
]
;

CrossTmp:
noconcatenate
load TmpID as I,* resident Departments;

drop table Departments;
drop fields TmpID,[Department Type],[Parent ID] ,[Department ID];

Crosstable:
CrossTable(Tmp1, Tmp2)
load * resident CrossTmp;

drop table CrossTmp;
drop field Tmp1;

 join 


LOAD Department_ID as I,* INLINE [
    Project ID, Department_ID
    001, 240
    002, 150
    003, 550
    004, 240
];


output:
noconcatenate

load  Tmp2 as [Department ID],count([Project ID]) as NbrProject resident Crosstable group by Tmp2;

drop table Crosstable;

output :

[bashar_f]

Hello @jwjackso 

thanks for your reply, I applied this HierarchyBelongsTo function, and its partly the solution I guess, because it helps me sum up the ancestors and those below them, but  something needs to be added to it and I have no idea what. The problem is that the hierarchy isn't fixed, you could have a sector that's a parent to base department, and the projects could be linked directly to a sector, or to a general department. What I'm getting with this solution is a sector on each level of department types, but it should stay the same type. Here's the actual data set attached for your reference, pardon the names of the departments as they are in Arabic, but they are not necessary.

The confusing part lies in the levels of ancestry.

Thanks so much for your time.

This is the code I used for Departments

Departments:
LOAD
DepartmentID,
Department,
DepartmentTypeID,
[Department Type],
ParentDepartmentID
FROM [Departments.qvd]
(qvd);

DepartmentBelongsTo:
HierarchyBelongsTo(DepartmentID,ParentDepartmentID,[Department],AncestorID,AncestorName,DepthDiff)
Load
DepartmentID,
Department,
ParentDepartmentID,
DepartmentTypeID,
[Department Type]
Resident Departments;