topic Re: Trouble with recursive logic in building a hierarchy qvd in App Development

Trouble with recursive logic in building a hierarchy qvd

shep_work — Tue, 06 May 2025 21:53:58 GMT

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;

Re: Trouble with recursive logic in building a hierarchy qvd

SRA — Wed, 07 May 2025 08:28:16 GMT

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

Re: Trouble with recursive logic in building a hierarchy qvd

ali_hijazi — Wed, 07 May 2025 11:23:54 GMT

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

];

Re: Trouble with recursive logic in building a hierarchy qvd

hic — Wed, 07 May 2025 12:35:44 GMT

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

Re: Trouble with recursive logic in building a hierarchy qvd

shep_work — Wed, 07 May 2025 13:18:51 GMT

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.

Re: Trouble with recursive logic in building a hierarchy qvd

shep_work — Wed, 07 May 2025 15:49:04 GMT

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;