Table

[kabbas]

.

[Qrishna · Accepted solution]

Please try below:

Employee:

load ParentItemID as Supervisor,  ChildItemID as Employee, ChildItemQty as Qty;

//BOMHierarchy:

load * inline [

ParentItemID,ChildItemID,ChildItemQty

1001,1002,2

1001,1003,1

1003,1004,4

1003,1005,6

1005,1006,10

1005,1007,8

1007,1008,12

];

//1.1 Find the Top of the chain Employee that has no supervisor and concatenate to the source table, in case it is not available in the table.

//concatenate(Employee)

TopEmp:

Load distinct 

     Supervisor  as  Employee,   // as chief is also an employee of the organization

     ''                     as  Supervisor   // the chief doesnt have any supervisor

Resident Employee 

where not exists(Employee, Supervisor);

//1.2 store the ChildItemID from topemp in a variable to define who the boss is

let vBossID = peek('Employee',0,'TopEmp');

concatenate(Employee)

load * resident TopEmp;

drop table TopEmp;

//1.3. Creating ids from the given source to keep the records unique when paths are defined

NoConcatenate

employee:

Load 

AutoNumber(Supervisor,'emp') as supervisor_id,

           Supervisor                                                     as supervisor,

AutoNumber(Employee,'emp')            as employee_id,

           Employee                                                      as employee,

           Qty

Resident Employee;

Drop Table Employee;

rename table employee to Employee;

//---------------------------------------------------//

// 1.4. Initialize the range of employee IDs to process

Heirarchy_Temp:

Hierarchy(employee_id, SupID, EmpName, SupName, EmpName, PathName, '\', Depth) 

LOAD employee_id,

             supervisor_id    as  SupID,

             employee           as  EmpName

RESIDENT Employee

where  employee >=  $(vBossID)

;

//1.5. Rearrange by Depth

NoConcatenate

Heirarchy:

Load *

Resident Heirarchy_Temp

where Depth > 1 and Depth <> previous(Depth)

Order by Depth;

Drop Table Heirarchy_Temp;

//---------------------------------------------------//

//1.6. Loop through each Employee id an concatenate to the BossID heirarchy table

FOR Each vEmployee in FieldValueList('employee')

              // Create a temporary hierarchy table based on the current employee ID

             Heirarchy_Temp:

             Hierarchy(employee_id, SupID, EmpName, SupName, EmpName, PathName, '\', Depth) 

             LOAD employee_id,

                         supervisor_id AS SupID,

                         employee AS EmpName

             RESIDENT Employee

             WHERE employee >= $(vEmployee) and employee >  $(vBossID); // Use the current employee value from the loop

             // Check if the temporary table was created successfully

             IF NoOfRows('Heirarchy_Temp') > 0 THEN

                 // Rearrange by Depth

                 Concatenate(Heirarchy)

                 LOAD *

                 RESIDENT Heirarchy_Temp

                 WHERE Depth > 1 AND Depth <> PREVIOUS(Depth)

                 ORDER BY Depth;

                 // Drop the temporary hierarchy table

                 DROP TABLE Heirarchy_Temp;

             ELSE

                 TRACE No rows found for employee ID: $(vEmployee);

                 // Optionally, handle the case when no rows are found (e.g., logging, error handling)

ENDIF;

Next vEmployee; // End of For Each loop

EXIT SCRIPT;