This is an old revision of the document!
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 a warning.
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 |