Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Experts,
I have a csv file that has following information:
emp id, emp name, manager id
1, A, -
2, B, A
3, C, B
4, D, C
I understand that using hierarchy I can create depth
emp id, emp name, manager id, Path Name, depth
1, A, -, A, 1
2, B, A,A/B, 2
3, C, B, A/B/C, 3
4, D, C, A/B/C/D, 4
I need a filter condition where if I select emp- A and level as 1, I should only get B
If I select emp-A and level 2, I should get B and C
If I select emp B and level 3, I should get B, C and D
Any idea how can I implement this?
Not really sure what you are trying to achieve, but this should guide you
Adjust your script to include
FOR i=1 to $(vNofEmpLevel)
NewEmployeeTbl:
LOAD empid, empname$(i) AS LineManagers, $(1) AS Level
Resident Hiertest
;
NEXT;
Data:
Load empid,LineManagers, Level Resident Temp_data
Where empname <> LineManagers AND Len(LineManagers) >0
;
LEFT JOIN (Data)
LOAD Max(Level) AS maxLevel,
empid
Resident Data
GROUP BY empid
;
Dataset:
LOAD empid,LineManagers,IF(flag=0, 'Direct Report', flag & ' Level Below') AS Desc
;
LOAD empid,LineManagers, num(maxLevel)-num(Level) As flag
Resident Data ;
Drop Table Data:
You need append the previous script. I only added Dataset table.
Hello @jerifortune ,
I copied the code given above:
[Hierarchy]:
Noconcatenate
Hierarchy(NodeID, ParentID, NodeName, ParentName, NodeName, PathName, '\', Depth)
LOAD
num(EmployeeID) as NodeID,
if([CustomMgrID] = '-', '',CustomMgrID) as ParentID,
Fullname as NodeName
Resident Customer_Managers_withID;
tempMaxLevel:
LOAD MAX(Depth) AS #ofEmpLevel Resident Hierarchy;
Let vNofEmpLevel= Peek('#ofEmpLevel',0,'tempMaxLevel');
FOR i=1 to $(vNofEmpLevel)
NewEmployeeTbl:
LOAD NodeID, NodeName$(i) AS LineManagers , $(1) AS Level
Resident Hierarchy;
//Resident Hiertest;
NEXT;
NoConcatenate
Temp_data:
Load * Resident NewEmployeeTbl;
Left Join (Temp_data)
Load NodeID, NodeName Resident Hierarchy;
Drop table NewEmployeeTbl;
NoConcatenate
Data:
Load NodeID,LineManagers Resident Temp_data
Where NodeName <> LineManagers AND Len(LineManagers) >0
;
LEFT JOIN (Data)
LOAD Max(Level) AS maxLevel,
NodeID
Resident Data
GROUP BY NodeID
;
Dataset:
LOAD NodeID,LineManagers,IF(flag=0, 'Direct Report', flag & ' Level Below') AS Desc
;
LOAD NodeID,LineManagers, num(maxLevel)-num(Level) As flag
Resident Data ;
Drop Table Data;
Drop Table Temp_data;
//Drop table [Customer_Managers_withID];
exit script;
But I am getting the error as below:
Honestly speaking I did not understand the code completely so not able to fix the error.
My use case is: Two columns in filter: Line_Manager & Levels_Below
So, if an employee in Line_Manager is selected (say he/she is on depth 3), the column Levels_Below should show depth 4 under him as Direct Reportee, 5 as Level 1 and 6 as Level 3 and so on.
If an employee in Line_Manager is selected (say he/she is on depth 4), the column Levels_Below should show depth 5 under him as Direct Reportee, 6 as Level 1 and 7 as Level 3 and so on.
Hiertest:
Hierarchy
(empid, managerid, empname, Manager, empname , Employee_Hierarchy,,[Depth])
Load
empid, managerid, empname
;
LOAd * Inline [
empid, empname, managerid
1, A, -
2, B, 1
3, C, 2
4, D, 3
]
;
tempMaxLevel:
LOAD MAX(Depth) AS #ofEmpLevel Resident Hiertest;
Let vNofEmpLevel= Peek('#ofEmpLevel',0,'tempMaxLevel');
FOR i=1 to $(vNofEmpLevel)
NewEmployeeTbl:
LOAD empid, empname$(i) AS LineManagers, $(i) AS Level
Resident Hiertest
;
NEXT;
NoConcatenate
Temp_data:
Load * Resident NewEmployeeTbl;
Left Join (Temp_data)
Load empid, empname Resident Hiertest;
Drop table NewEmployeeTbl;
NoConcatenate
tempData:
Load empid,LineManagers, Level Resident Temp_data
Where empname <> LineManagers AND Len(LineManagers) >0
;
LEFT JOIN (tempData)
LOAD Max(Level) AS maxLevel,
empid
Resident tempData
GROUP BY empid
;
Data:
LOAD empid,LineManagers,IF(flag=0, 'Direct Report', flag & ' Level Below') AS Desc
;
LOAD empid,LineManagers, num(maxLevel)-num(Level) As flag
Resident tempData ;
Drop Table Temp_data, tempData;
Drop table Hiertest;