transformations:hierarchypath
Table of Contents
HIERARCHY PATH
Category: Transform / Advanced
Description
This action generates a pipe-delimited hierarchy path for every value in a column that references a parent value in the same table.
Use cases
Use this action to build parent-child relationships such as company organizational charts.
Action settings
| Setting | Description |
|---|---|
| Primary key | Select the column containing the unique key values to build the hierarchy from. |
| Parent key | Select the column containing the parent keys. |
| Build path with | Choose whether the output hierarchy path will contain labels (and select the column containing those labels) or the keys. Options: Labels or Keys. |
| Reverse the paths | Checking this will reverse the output order of the keys or labels in the hierarchy path. |
Remarks
All keys in the selected Primary key column must be unique.
The parent-child relationship between the primary keys and parent keys cannot create a recursive relationship (e.g. Key01's parent is Key10, and Key10's parent is Key01). This will result in an error.
The Split delimited text into columns action can be used to parse the path keys into columns.
Examples
Example #1
Generate hierarchy paths based on the relationship table, below, with the path on Names.
Before (source table)
| Primary | Parent | Name |
|---|---|---|
| Key01 | Key10 | Bob |
| Key02 | Key10 | Ann |
| Key03 | Key10 | Ira |
| Key10 | Key20 | Aya |
| Key20 | Bruce |
After (result table)
| Primary | Parent | Name | Path |
|---|---|---|---|
| Key01 | Key10 | Bob | Bob|Aya|Bruce |
| Key02 | Key10 | Ann | Ann|Aya|Bruce |
| Key03 | Key10 | Ira | Ira|Aya|Bruce |
| Key10 | Key20 | Aya | Aya|Bruce |
| Key20 | Bruce | Bruce |
Action parameters
Primary key: Primary
Parent key: Parent
Build path with: Labels(Names)
Example #2
Generate hierarchy paths based on the relationship table, below, with the path on keys, and show the path in reverse order.
Before (source table)
| Primary | Parent | Name |
|---|---|---|
| Key01 | Key10 | Bob |
| Key02 | Key10 | Ann |
| Key03 | Key10 | Ira |
| Key10 | Key20 | Aya |
| Key20 | Bruce |
After (result table)
| Primary | Parent | Name | Path |
|---|---|---|---|
| Key01 | Key10 | Bob | Key01|Key10|Key20 |
| Key02 | Key10 | Ann | Key02|Key10|Key20 |
| Key03 | Key10 | Ira | Key03|Key10|Key20 |
| Key10 | Key20 | Aya | Key10|Key20 |
| Key20 | Bruce | Key20 |
Action parameters
Primary key: Primary
Parent key: Parent
Build path with: Keys
Reverse the paths
See also
transformations/hierarchypath.txt · Last modified: 2025/01/15 21:40 by craigt