Thanks

[Qrishna]

is this what you are looking for? (i changed the field names Parent to supervisor and child to Employee for my understanding).

(i see you want all the non metric fields like BOM Level 1 Code, BOM Level 1 Name, BOM Level 1 Item Group Code etc which i havent written code for the thoses joins yet, this code being a tedious n laborious)

[kabbas]

hi @Qrishna thanks but i look for like that

looking for Parent child relation every id wise

ParentItemID	L1	L2	L3	L4
1001	1002
1001	1003	1004
1001	1003	1005	1006
1001	1003	1005	1007	1008
1003	1004
1003	1005	1006
1003	1005	1007	1008
1005	1006
1005	1007	1008
1007	1008

[Qrishna]

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.

[kabbas]

Thanks @Qrishna how about total quantity at lowest level 

[Qrishna]

Will post the script soon for that, as you changed the requirement from that of the original post in you reply, i had to rewrite the entire code and forgot about TotalQty. in the mean time, post the expected Totalqty column values in your table, that would be helpful.

[kabbas]

I have attached the file which have requirements 

[Qrishna]

I just updated the code with the Objective2 - TotalQty.

Good luck with your Assignment.!

[kabbas · Accepted solution]

Thanks bro for the help it really kind of you 

[Qrishna]

No Worries, please close the thread marking the right solution.