User Tools

Site Tools



Category: Transform / Advanced


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

Primary keySelect the column containing the unique key values to build the hierarchy from.
Parent keySelect the column containing the parent keys.
Build path withChoose whether the output hierarchy path will contain labels (and select the column containing those labels)
or the keys. Options: Labels or Keys.
Reverse the pathsChecking this will reverse the output order of the keys or labels in the hierarchy path.


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.


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


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

See also

transformations/hierarchypath.txt · Last modified: 2023/04/21 03:48 by dmitry

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki