Self-Looping Column (Hierarchy Based on One Column of Data)

Version

Application Scenario

When a series of hierarchical data has its own regular ID numbers, you can use self-looping columns to establish the hierarchical relationship among the data.

For example, the head office has a table containing the ID numbers of the branch offices, departments, and staff, which follow the specific rule as shown in the following figure.

• Different levels (such as subsidiaries, departments, and staff) have different digits in their ID      numbers.

• Different levels (such as subsidiaries, departments, and staff) have the same digit in their ID      numbers.

You can create a group table and a detail table based on the ID numbers to display the hierarchical relationship, as shown in the following figure.

Usage Scope

The Self-Looping Column function is available to basic tables and not supported for self-service datasets and direct-connected data tables.

Data Preparation

1. After downloading the example dataDepartment 1.xlsx, select a folder and upload the example data to public data, as shown in the following figure.

2. Click Edit, as shown in the following figure.

3. You need to ensure that the field type of the ID column is Text as the column is the key field that distinguishes the hierarchical relationship. Otherwise, you need to convert the field type, as shown in the following figure.

Hierarchy Building

1. Add a self-looping column, set ID Column as Hierarchical Dependence to ID, and Hierarchical Result Field to Name, and click Build Association, as shown in the following figure.

2. The ID column has three different lengths, so the system automatically divides them into three levels. Modify the level names to Branch Office, Department, and Staff, respectively.

3. Click Save and exit the editing page.

You can preview tables with self-looping columns in Preview Data.

Now you can use the hierarchical fields generated by the self-looping columns to create self-service datasets and dashboards. The following section will use the detail table component as an example for explanation.

Component Creation

When using the above table to create a group table, you are advised to filter out empty values in the lowest hierarchy for ease of viewing. 

Procedure

1. You can download the example dataDepartment 3.xlsx and complete the data preparation based on section "Data Preparation."

2. Build self-looping columns. When the hierarchy lengths of ID Column are consistent, you need to set the hierarchy length. When the hierarchy length is set to 2, the ID field with a length of 6 digits will be divided into three levels. Click Save.

3. The steps to create components can be found in section "Component Creation."

Effect Display

For details, see section "Application Scenario."