topic Re: Multiple Hierarchy Levels with Parents Including Children Values in App Development

Multiple Hierarchy Levels with Parents Including Children Values

bashar_f — Sat, 16 Nov 2024 02:13:28 GMT

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.

Re: Multiple Hierarchy Levels with Parents Including Children Values

jwjackso — Fri, 05 Jun 2020 15:59:55 GMT

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.

Re: Multiple Hierarchy Levels with Parents Including Children Values

Taoufiq_Zarra — Fri, 05 Jun 2020 17:04:44 GMT

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

Re: Multiple Hierarchy Levels with Parents Including Children Values

bashar_f — Sat, 06 Jun 2020 18:00:45 GMT

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;