Hierarchy
The Hierarchy() function returns the children associated with a parent entity on a specific date. In Quintessence, a hierarchy consists of the same type of relationship repeated from the parent to the child, from that child to the next, and so on.
Related functions: ReverseHierarchy(), Relationship(), ReverseRelationship()
Syntax:
Qx.Hierarchy(Entity Codes, Relationship, Levels, IAAD, Projection, Headers, Source, ShowError)- In Excel with Quintessence addin versions prior to 25.1, use:
Hierarchy() - In the Quintessence Editor (Studio) using Quintessence language, use:
Hierarchy() - In the Quintessence Editor (Studio) using Python language, use:
Qx.Hierarchy()
ⓘ See parameter descriptions
| Entity Codes | Function requires an entity code that represents the root of a hierarchy. |
| Relationship | The relationship between the child and parent entities. |
| Levels (optional) | Specifies which levels to display (the first level is 0). |
| IAAD (optional) | The as at date specifies the point in time for the information. Relationships established after this date will not be shown. |
| Projection (optional) | The projection of the hierarchy. Available options are: none, parents, or leaves. |
| Headers (optional) | Specifies whether to include column headers in the output. Options are none and column. |
| Source (optional) | The source provider of the data. |
| ShowError (optional) | If TRUE, an error message is displayed when the function fails. |
Remarks:
- Unless changed by an administrator, the default parameters are:
- Levels: all available levels
- IAAD: today
- Projection:
none - Headers:
none - Source: determined by implicit or explicit defaults, if available.
- It is always safer to specify the source parameter rather than relying on the default. Leaving this blank not only depends on explicit defaults set by the administrator, but also on data being loaded, which may cause conflicts with implicit defaults.
Examples:
The examples provided below are based on fictitious data for illustrative purposes only.
> Example 1: Return the 5-tier classification hierarchy of FofxPortfolio.
The relationship of interest is the ICB Classification, and the data is provided by the source InternalDataProvider.
=Qx.Hierarchy("FofxPortfolio","ICB Classification",,,,,"InternalDataProvider")| FofxPortfolio | Basic Materials | Basic Resources | Forestry & Paper | Forestry | YRK |
| FofxPortfolio | Basic Materials | Basic Resources | Industrial Metals | Aluminum | HLM |
| FofxPortfolio | Basic Materials | Basic Resources | Mining | General Mining | AGL |
| FofxPortfolio | Food & Beverage | Food Producers | Farming & Fishing | RCL | |
| FofxPortfolio | Food & Beverage | Food Producers | Food Products | AVI |
> Example 2: Display specific levels of the FofxPortfolio hierarchy.
The relationship of interest is the ICB Classification, and the data is provided by the source InternalDataProvider. In this example, the levels displayed are 1 and 3 (see Example 1 for the full hierarchy result for comparison).
=Qx.Hierarchy("FofxPortfolio","ICB Classification", "1,3",,,"column", "InternalDataProvider")| 1 | 3 |
| Basic Materials | Forestry & Paper |
| Basic Materials | Industrial Metals |
| Basic Materials | Mining |
| Food & Beverage | Farming & Fishing |
| Food & Beverage | Food Products |
> Example 3: Using leaf and parent projections
Selecting parents for the projection carries the parent value forward so that the leaf nodes appear at the same level (see Example 1 for the full hierarchy result for comparison).
=Qx.Hierarchy("FofxPortfolio","ICB Classification",,,"parents",,"InternalDataProvider")| FofxPortfolio | Basic Materials | Basic Resources | Forestry & Paper | Forestry | YRK |
| FofxPortfolio | Basic Materials | Basic Resources | Industrial Metals | Aluminum | HLM |
| FofxPortfolio | Basic Materials | Basic Resources | Mining | General Mining | AGL |
| FofxPortfolio | Food & Beverage | Food Producers | Farming & Fishing | Farming & Fishing | RCL |
| FofxPortfolio | Food & Beverage | Food Producers | Food Products | Food Products | AVI |
Selecting leaves for the projection carries the leaf nodes forward so that all leaf nodes appear at the same level.
=Qx.Hierarchy("FofxPortfolio","ICB Classification",,,"leaves",,"InternalDataProvider")| FofxPortfolio | Basic Materials | Basic Resources | Forestry & Paper | Forestry | YRK |
| FofxPortfolio | Basic Materials | Basic Resources | Industrial Metals | Aluminum | HLM |
| FofxPortfolio | Basic Materials | Basic Resources | Mining | General Mining | AGL |
| FofxPortfolio | Food & Beverage | Food Producers | Farming & Fishing | RCL | RCL |
| FofxPortfolio | Food & Beverage | Food Producers | Food Products | AVI | AVI |