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: 
SelinaM
Contributor
Contributor

Self-referencing table / Hierarchy / Pivot table

Hello!

I'm trying to build a data model with a table that references itself and I'm struggling to display this hierarchy in a Pivottable.

For example there is a field called projectID, then some measures like projectName, baseLineHours, planHours... , and a field parentID.

Example of data modelExample of data model

 
So there is an hierarchical structure on the table. The parent itself is also a record within the table - so the values in parentID are a subset of the values in projectID.

Only a few projects have a parentID and a parent can have multiple subprojects. But one subproject can't be a parent of another project, so the depth of the hierarchy is only 2. 

I hava already used the Hierarchy function to determine the subprojects to the parent (and vice versa) in an additional table und to add fields "isChild" and "isParent" to the original table (which is useful for a different diagram sheet).


I now want to display all parent Projects with all other dimensions and measures in an table (or pivot table) - and then will be able to click open and display the subprojects to a specific master project aswell. Just like it's possible with a pivot table.
The problem with this is, that the baselineHours or planHours of a Master aren't the sum of it's subprojects. 
So in the table, for the Master projects Qlik Sense should pick the row where the productID is the parentID, but otherwise just the ID of the subproject as usual.

The rows for K1 and M1 should now contain the respective records of K1 or M1 instead of the wrong record or '-'The rows for K1 and M1 should now contain the respective records of K1 or M1 instead of the wrong record or '-'


Has anyone an idea how to do that? Maybe with an different approach in data model or  choice of the diagram.

Thanks in advance!!

Selina

0 Replies