Self-referencing table / Hierarchy / Pivot table

SelinaM — Thu, 18 Apr 2019 11:50:54 GMT

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 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 '-'

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

topic Self-referencing table / Hierarchy / Pivot table in App Development

Self-referencing table / Hierarchy / Pivot table