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.
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: Generate hierarchy paths based on the Input table, below.
Input table:
| Primary | Parent | Name |
|---|---|---|
| Key01 | Key10 | Bob |
| Key02 | Key10 | Sue |
| Key03 | Key10 | Erica |
| Key04 | Key10 | Yelena |
| Key05 | Key11 | Chris |
| Key06 | Key11 | John |
| Key10 | Key20 | Tia |
| Key11 | Key20 | Mariko |
| Key20 | Bruce |
Action parameters:
Primary key: "Primary"
Parent key: "Parent"
Build path with: "Keys"
Reverse the paths: Unchecked
Result:
| Primary | Parent | Name | Path |
|---|---|---|---|
| Key01 | Key10 | Bob | Key20|Key10|Key01 |
| Key02 | Key10 | Sue | Key20|Key10|Key02 |
| Key03 | Key10 | Erica | Key20|Key10|Key03 |
| Key04 | Key10 | Yelena | Key20|Key10|Key04 |
| Key05 | Key11 | Chris | Key20|Key11|Key05 |
| Key06 | Key11 | John | Key20|Key11|Key06 |
| Key10 | Key20 | Tia | Key20|Key10 |
| Key11 | Key20 | Mariko | Key20|Key11 |
| Key20 | Bruce | Key20 |
Reverse the paths: Checked
| Primary | Parent | Name | Path |
|---|---|---|---|
| Key01 | Key10 | Bob | Key01|Key10|Key20 |
| Key02 | Key10 | Sue | Key02|Key10|Key20 |
| Key03 | Key10 | Erica | Key03|Key10|Key20 |
| Key04 | Key10 | Yelena | Key04|Key10|Key20 |
| Key05 | Key11 | Chris | Key05|Key11|Key20 |
| Key06 | Key11 | John | Key06|Key11|Key20 |
| Key10 | Key20 | Tia | Key10|Key20 |
| Key11 | Key20 | Mariko | Key11|Key20 |
| Key20 | Bruce | Key20 |
Build path with: "Labels" (using "Name" column)
Reverse the paths: Unchecked
| Primary | Parent | Name | Path |
|---|---|---|---|
| Key01 | Key10 | Bob | Bruce|Tia|Bob |
| Key02 | Key10 | Sue | Bruce|Tia|Sue |
| Key03 | Key10 | Erica | Bruce|Tia|Erica |
| Key04 | Key10 | Yelena | Bruce|Tia|Yelena |
| Key05 | Key11 | Chris | Bruce|Mariko|Chris |
| Key06 | Key11 | John | Bruce|Mariko|John |
| Key10 | Key20 | Tia | Bruce|Tia |
| Key11 | Key20 | Mariko | Bruce|Mariko |
| Key20 | Bruce | Bruce |