Trouble with recursive logic in building a hierarchy qvd

[shep_work]

I am having a bit of a circular logic issue in Qlik Sense Data Load Script that I can't seem to answer fully. I need to build a hierarchy of 'levels' based solely on ID, First_Name, Last_Name, Supervisor_ID, and Supervisor_Name. Where there is a singular top level person that represents 'level 4' and each person who reports to them is a 'level 5' and each person who reports to a 'level 5' is a 'level 6' and so on down to level 7. Where if a person is missing a given bottom tier they are that tier for themselves. I keep going in circles of either getting everyone the same 'level 4' and 'level 5' but blanks in everything else or only their own levels with no other level populated. Please help if you can, Example data and script below. TIA.

sample data:
ID,FIRST_NAME,LAST_NAME,SUPERVISOR_ID,SUPERVISOR_NAME
100,Jill,Hill,1234567890,Rachel Pail
210,Don,Mountain,100,Jill Hill
1234567890,Rachel,Pail,1234567890,Rachel Pail
310,Jennifer,Story,210,Don Mountain
410,Jackson,Hilltop,310,Jennifer Story
500,Al,Mudd,310,Jennifer Story

desired outcome:
ID,FIRST_NAME,LAST_NAME,SUPERVISOR_ID,SUPERVISOR_NAME,LEVEL,HC_LEVEL_4,HC_LEVEL_5,HC_LEVEL_6,HC_LEVEL_7
1234567890,Rachel,Pail,1234567890,Rachel Pail,4,Rachel Pail,,,
100,Jill,Hill,1234567890,Rachel Pail,5,Rachel Pail,Jill Hill,,
210,Don,Mountain,100,Jill Hill,6,Rachel Pail,Jill Hill,Don Mountain,
310,Jennifer,Story,210,Don Mountain,7,Rachel Pail,Jill Hill,Don Mountain,Jennifer Story
410,Jackson,Hilltop,310,Jennifer Story,7,Rachel Pail,Jill Hill,Don Mountain,Jackson Hilltop
500,Al,Mudd,310,Jennifer Story,7,Rachel Pail,Jill Hill,Don Mountain,Al Mudd

actual outcome:
final_id,final_first_name,final_last_name,final_supervisor_id,final_supervisor_name,final_level,final_hc_level_4,final_hc_level_5,final_hc_level_6,final_hc_level_7
100,Jill,Hill,1234567890,Rachel Pail,4,Jill Hill,-,-,-
100,Jill,Hill,1234567890,Rachel Pail,5,Rachel Pail,Jill Hill,-,-
210,Don,Mountain,100,Jill Hill,6,Rachel Pail,-,Don Mountain,-
310,Jennifer,Story,210,Don Mountain,7,Rachel Pail,-,-,Jennifer Story
410,Jackson,Hilltop,310,Jennifer Story,8,Rachel Pail,-,-,-
1234567890,Rachel,Pail,1234567890,Rachel Pail,4,Rachel Pail,-,-,-

data load script wip:
// Step 1: Load the source QVD data
Headcount_Table:
LOAD
    id AS headcount_id,
    FIRST_NAME AS headcount_first_name,
    LAST_NAME AS headcount_last_name,
    SUPERVISOR_id AS headcount_supervisor_id,
    SUPERVISOR_NAME AS headcount_supervisor_name
INLINE [
    id, FIRST_NAME, LAST_NAME, SUPERVISOR_id, SUPERVISOR_NAME
    100, Jill, Hill, 1234567890, Rachel Pail
    210, Don, Mountain, 100, Jill Hill
    1234567890, Rachel, Pail, 1234567890, Rachel Pail
    310, Jennifer, Story, 210, Don Mountain
    410, Jackson, Hilltop, 310, Jennifer Story
    500, Al, Mudd, 310, Jennifer Story
];

// Step 2: Assign the top-level supervisor (Level 4)
Hierarchy_Assigned:
LOAD
    headcount_id AS hierarchy_id,
    headcount_first_name AS hierarchy_first_name,
    headcount_last_name AS hierarchy_last_name,
    headcount_supervisor_id AS hierarchy_supervisor_id,
    headcount_supervisor_name AS hierarchy_supervisor_name,
    4 AS hierarchy_level,
    headcount_first_name & ' ' & headcount_last_name AS hierarchy_hc_level_4,
    NULL() AS hierarchy_hc_level_5,
    NULL() AS hierarchy_hc_level_6,
    NULL() AS hierarchy_hc_level_7
RESIDENT Headcount_Table
WHERE headcount_supervisor_id = '1234567890';

// Store the Level 4 table into a QVD for interim processing
STORE Hierarchy_Assigned INTO [$(vMainPath)QVD_files/Hierarchy_Assigned.qvd] (qvd);

// Step 3: Iteratively assign levels and populate hierarchy fields
LET vRowsAdded = 1; // Initialize a variable to track if rows are added

DO WHILE vRowsAdded > 0

    // Load the next level of employees
    Next_Level:
    LOAD
        headcount_id AS hierarchy_id,
        headcount_first_name AS hierarchy_first_name,
        headcount_last_name AS hierarchy_last_name,
        headcount_supervisor_id AS hierarchy_supervisor_id,
        headcount_supervisor_name AS hierarchy_supervisor_name,
        Peek('hierarchy_level', -1, 'Hierarchy_Assigned') + 1 AS hierarchy_level,
        Peek('hierarchy_hc_level_4', -1, 'Hierarchy_Assigned') AS hierarchy_hc_level_4,
        IF(Peek('hierarchy_level', -1, 'Hierarchy_Assigned') = 4, headcount_first_name & ' ' & headcount_last_name) AS hierarchy_hc_level_5,
        IF(Peek('hierarchy_level', -1, 'Hierarchy_Assigned') = 5, headcount_first_name & ' ' & headcount_last_name) AS hierarchy_hc_level_6,
        IF(Peek('hierarchy_level', -1, 'Hierarchy_Assigned') = 6, headcount_first_name & ' ' & headcount_last_name) AS hierarchy_hc_level_7
    RESIDENT Headcount_Table
    WHERE headcount_supervisor_id = Peek('hierarchy_id', -1, 'Hierarchy_Assigned');

    // Check if the Next_Level table has rows
    LET vRowsAdded = NoOfRows('Next_Level');

    // If rows were added, store and concatenate them
    IF vRowsAdded > 0 THEN
        // Store the next level into a QVD for interim processing
        STORE Next_Level INTO [$(vMainPath)QVD_files/Next_Level.qvd] (qvd);

        // Concatenate the new level into the Hierarchy_Assigned table
        CONCATENATE (Hierarchy_Assigned)
        LOAD
            *
        RESIDENT Next_Level;

        // Drop the Next_Level table to free memory
        DROP TABLE Next_Level;
    END IF

LOOP

// Step 4: Create the final table with hierarchical levels and management structure
Final_Table:
LOAD
    hierarchy_id AS final_id,
    hierarchy_first_name AS final_first_name,
    hierarchy_last_name AS final_last_name,
    hierarchy_supervisor_id AS final_supervisor_id,
    hierarchy_supervisor_name AS final_supervisor_name,
    hierarchy_level AS final_level,
    hierarchy_hc_level_4 AS final_hc_level_4,
    hierarchy_hc_level_5 AS final_hc_level_5,
    hierarchy_hc_level_6 AS final_hc_level_6,
    hierarchy_hc_level_7 AS final_hc_level_7
RESIDENT Hierarchy_Assigned;

// Store the final table into a QVD
//STORE Final_Table INTO [$(vMainPath)QVD_files/Final_Table.qvd] (qvd);

// Drop intermediate tables
DROP TABLE Headcount_Table;
DROP TABLE Hierarchy_Assigned;

[SRA · Accepted solution]

Hi,

Did you tried to use the Hierarchy 

Headcount_Table:
LOAD
id AS headcount_id,
FIRST_NAME AS headcount_first_name,
LAST_NAME AS headcount_last_name,
FIRST_NAME & ' ' & LAST_NAME AS headcount_full_name,
SUPERVISOR_id AS headcount_supervisor_id,
SUPERVISOR_NAME AS headcount_supervisor_name
INLINE [
id , FIRST_NAME , LAST_NAME , SUPERVISOR_id , SUPERVISOR_NAME
100 , Jill , Hill , 1234567890 , Rachel Pail
210 , Don , Mountain , 100 , Jill Hill
1234567890 , Rachel , Pail , 1234567890 , Rachel Pail
310 , Jennifer , Story , 210 , Don Mountain
410 , Jackson , Hilltop , 310 , Jennifer Story
500 , Al , Mudd , 310 , Jennifer Story
];

T_Hierarchy:
Hierarchy(headcount_id,headcount_supervisor_id, headcount_full_name)
Load
*
Resident Headcount_Table;

Drop Table Headcount_Table;

You will get this (in yellow, new columns added by the prefix Hierarchy before the load) :

More info about Hierarchies : https://community.qlik.com/t5/Member-Articles/Hierarchies/ta-p/1487801

Regards

SRA

[ali_hijazi]

there is already an out-of-the-box function named Hierarchy that solved your requirement:

Hierarchy

The hierarchy prefix is used to transform a parent-child hierarchy table to a table that is useful in a Qlik Sense data model. It can be put in front of a LOAD or a SELECT statement and will use the result of the loading statement as input for a table transformation.

The prefix creates an expanded nodes table, which normally has the same number of records as the input table, but in addition each level in the hierarchy is stored in a separate field. The path field can be used in a tree structure.

Syntax:  

Hierarchy (NodeID, ParentID, NodeName, [ParentName, [PathSource, [PathName, [PathDelimiter, Depth]]]])(loadstatement | selectstatement) 

the first 3 parameters are obligatory, the remaining are optional:

Example:  

Hierarchy(NodeID, ParentID, NodeName, ParentName, NodeName, PathName, '\', Depth) LOAD * inline [

NodeID, ParentID, NodeName

1, 4, London

2, 3, Munich

3, 5, Germany

4, 5, UK

5, , Europe

];

[hic]

See more on
https://community.qlik.com/t5/Design/Unbalanced-n-level-hierarchies/ba-p/1474325
https://community.qlik.com/t5/Member-Articles/Hierarchies/ta-p/1487801

HIC

PS You will also find all this in Qlik according to HIC 

[shep_work]

Thank you sir, I've seen your avatar and work around, but didn't know you published a book.  I'll give the links a shot.

[shep_work]

TY, your code got me the closest to what I was needing; and no I hadn't heard of Hierarchy before, so I've been doing things the hard way for a while :)-.

Small sample of what I am working with now.

// 1) Raw data
Headcount_Raw:
LOAD
  ID           AS ID,
  FIRST_NAME   AS FIRST_NAME,
  LAST_NAME    AS LAST_NAME,
  SUPERVISOR_ID    AS SUPERVISOR_ID,
  FIRST_NAME & ' ' & LAST_NAME  AS LEVEL
INLINE [
  ID, FIRST_NAME, LAST_NAME, SUPERVISOR_ID
  100, Jill, Hill,              1234567890
  210, Don,  Mountain,          100
  1234567890, Rachel, Pail,     1234567890
  310, Jennifer, Story,         210
  410, Jackson, Hilltop,        310
  500, Al, Mudd,                 310
];

// 2) Build hierarchy — load *only* ID, SUPERVISOR_ID, FullName.
//    Qlik will auto-generate Path and Depth fields here.

HierarchyTable:
Hierarchy(ID,SUPERVISOR_ID,LEVEL,ParentName,LEVEL,PathName,'|',Depth)
LOAD *
RESIDENT Headcount_Raw;

DROP TABLE Headcount_Raw;