Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bashar_f
Partner - Creator
Partner - Creator

Multiple Hierarchy Levels with Parents Including Children Values

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 IDDepartment ID
001240
002150
003550
004240


Departments
looks like this

Department IDParent IDDepartment Type
240150General Department
550240Base Department
150NullSector


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
2403
5501
1504

 

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.

1 Solution

Accepted Solutions
jwjackso
Specialist III
Specialist III

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;

HierarchyBelongsTo.PNG

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

View solution in original post

3 Replies
jwjackso
Specialist III
Specialist III

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;

HierarchyBelongsTo.PNG

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 :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
bashar_f
Partner - Creator
Partner - Creator
Author

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;