Skip to main content

Varicent ELT Help Center

Hierarchy

Abstract

Create a hierarchical structure to organize your data.

Create a hierarchical structure to organize your data.

Input and output

To use this tool, you need an existing data set in your pipe.

When you export the data, it organizes the data according to the parent and child outputs that you have set. You can add up to ten parent-child relationships by adding child columns to the child of the previous parent-child hierarchy.

When to use this tool

Use to create a hierarchical structure, such as creating a hierarchy to organize the data for all geographical locations in a data set.

Configuration

Configure each level by selecting a column for Key and a column for Name (optional). To name your top level, enter the name in the Top level name field.

If there are data issues in your input data that would break your hierarchy, a Hierarchy Error column is added to your data and a descriptive error shows next to the problematic data.

Preview returns results based on the first 10,000 rows of data. For data sets over 10,000 rows, build your pipe and switch from Preview to Last built to ensure that the entire data set is used to validate the hierarchy before cleaning your data.

Usage example

We want to create a hierarchy to organize the data for all locations listed in a data set. We can configure the hierarchy to define the following relationships:

  • Top level (Parent): Worldwide

    • Level 1:

      • Key: Country code

      • Name: Country name

    • Level 2:

      • Key: State or Province code

      • Name: State or Province name

    • Level 3:

      • Key: Zip code

      • Name: Zip or Postal code

    For example, we have a data set with all of the locations of an international organization, organized into a hierarchy:

    HierarchyExampleDec2023.png

Error messages

The Hierarchy tool can find the following errors in your data set:

  • Circular reference error: A higher-level value is trying to roll up to a lower-level value and vice versa. This error would appear in all records.

  • Duplicate key error: This key exists multiple times in the hierarchy. Ensure that each key only rolls up to one parent key and has a unique name. This error would appear in all records.

  • Multi-column error: Key exists in multiple columns. Ensure that each key is unique across all columns. This error would appear in all records.