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
//---------------------------------------------------//
//----------Update----------//
// 2. Find the Total Quantity for each pathname - Objective2
// 2.1. Split the PathName into individual employee IDs.
PathExploded:
LOAD distinct
PathName,
SubField(PathName, '\') AS Employee
RESIDENT Heirarchy;
// 2.2 Join the quantities to each employee in the path.
LEFT JOIN (PathExploded)
LOAD
employee as Employee,
Qty
RESIDENT Employee;
// 2.3. Aggregate to calculate the total quantity for each path.
left join(Heirarchy)
TotalPathQty:
Load *, SubField(PathName, '\', -1) AS MemberID;
LOAD
PathName,
SUM(Qty) AS TotalQty
RESIDENT PathExploded
GROUP BY PathName
order by PathName;
// Drop the intermediate exploded table if no longer needed.
DROP TABLE PathExploded;
EXIT SCRIPT;
Objective -1
//----------Update----------//
Objective-2 : you can verify the totals on the left to the Summing the Qty on the right.
