Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Direct/Indirect Subordinates under a Manager - From HR Data Set

P007
6 - Meteoroid

Hi Community,

 

I am stuck with an issue though I figured out the solution by going through one of the community solution but current fresh issue that I am facing is that when I am running the iterative macro it goes into infinite iterations because I have a record totaling upto 100,000 which is being iterate.

Major field that I am using is Emp ID, Emp Name, Supervisor Name , Supervisor ID.

 

Employee NameEmployee IDSupervisor NameSupervisor ID
Amandeep123Imran101
Pravir387Pankaj253
Bhupendra309Pankaj253
Bhatia388Pankaj253
Chaurasia299Sheeraz234
Sweta289Sheeraz234
Jasmeen201Imran101
Imran101Jitendra Dua100
Jitendra Dua100Nick98
Ronak124Nick98
Shivendra286Nick98
Vishesh125Imran101
Nick98Nancy56
Nancy56Toby7
toby7  
Pankaj253Amandeep123
Sheeraz234Amandeep123

 

NOTE - The points that needs to be noted here is that my data set is not limited to only this much record , because I have data set which contains 100,000 records all together.

 

Output which I want is :- Based on the user input (Either be it single or multiple - like for an example User input employee id as 101 , 253) so based on the input I should be getting the output se below :-

 

Supervisor NameSupervisor IDEmployee NameEmployee ID
Imran101Amandeep123
Imran101Jasmeen201
Imran101Vishesh125
Imran101Pankaj253
Imran101Sheeraz234
Imran101Pravir387
Imran101Bhupendra309
Imran101Bhatia388
Imran101Chaurasia299
Imran101Sweta289
Pankaj253Pravir387
Pankaj253Bhupendra309
Pankaj253Bhatia388

 

Also , you may visit the link which has similar datasets and similar solutions but the exact solution which I am looking for is still under search.

 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Producing-a-denormalised-output-from-H...

 

11 REPLIES 11
BrandonB
Alteryx
Alteryx

You might benefit from two of the weekly challenges that tackled very similar challenges: 

 

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-5-HR-Position-Finder-Application/td-p/36... 

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-12-Creating-an-HR-Hierarchy/td-p/36740 

 

There are many solutions posted which may be appropriate depending on what you are looking for. 

P007
6 - Meteoroid

Hi BrandonB,

 

Thank your for your prompt reply ..

 

But I have already gone through these solutions prior to posting the question in the community and the most appropriate one according to my need was posted here --

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Producing-a-denormalised-output-from-H...

 

As as I already pointed out , that when I am running it for a large data sets almost for 100,000 records the workflow goes into an endless iterative loop.

So, to get out of it - it should be iterating only those input which the user has provided through the analytical app (whether be it  1 or 2 or multiple inputs)

Nanoq
8 - Asteroid

I might misunderstand what youre trying to do, but it looks like you already have your solution? if you re arrange the columns so that supervisor is first, and then sort by supervisor ID, then employee ID, you should get the result you show as your input?
There might be some change you need made that is not apparent from your description, if so, let me know 

P007
6 - Meteoroid

Hi Nanoq,

 

Yeah , I have the logic and the workflow. It perfectly works for a smaller datasets but I am facing issue with larger datasets.

 

My dataset contains 100,000 number of rows with the column header same as above I have discussed.

The problem that I am facing is that when I run the workflow with my dataset as per the logic given this thread --

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Producing-a-denormalised-output-from-H...

 

Though it works perfect but it goes on and on with the iterations running - and I don't know when will this stop.

Now the thing is that I want -- is based on the user input like I have said above if the user input Employee ID as 101, 253 , I can get the output as stated above by building the analytical app.

 

And I am not able to figure out the logic for that like to contain the iterations and run for only those which the user has given in the input, if you have understood my query you can try once.

Pang_Hee_Choy
12 - Quasar

HI P007,

 

as you said your stuck at let the user input by multiple line. you can try this way. 

 

you can to change on 1 cells from the text box.

then use text to column to split to rows. 

Pang_Hee_Choy_0-1634898380436.png

Pang_Hee_Choy_1-1634898398035.png

 

Pang_Hee_Choy_2-1634898448210.png

 hope it helps.

 

 

here my workflow as well for your reference.

 

steps:

  1. filter employee without supervisor (top level / level 1)
  2. macro1: (iterate)
    1. vlookup with the lower level supervisor
    2. record management chain (level 1 - level 2 - level 3...)
  3. macro2:(batch)
    1. filter employee in management chain
    2. copy down the supervisor name and id.
  4. export file

 

P007
6 - Meteoroid

Hi Pang_Hee_Choy,

 

Thank you for the solution.

 

But the problem for me is still the same , when i run the workflow you provided on my dataset (which contains 100,000 rows of data) it still going into endless loop for iteration Macro1 , I have shared the screenshot for it when I was running with my original data , it was running for the last 39 minutes and still it didin't reached 50% of it also, I has to cancel it at last.

 

If you want me to send the dataset similar to 100,000 rows , I can send that too so that you can yourself try out what happens while you run the workflow.

P007
6 - Meteoroid

Hi 

P007_0-1634933944669.png

 There is the conversion error also and the iteration is going on and on and I don't know when this would end.

P007
6 - Meteoroid

Hi Nanoq,

 

Yeah , I have the logic and the workflow. It perfectly works for a smaller datasets but I am facing issue with larger datasets.

 

My dataset contains 100,000 number of rows with the column header same as above I have discussed.

The problem that I am facing is that when I run the workflow with my dataset as per the logic given this thread --

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Producing-a-denormalised-output-from-H...

 

Though it works perfect but it goes on and on with the iterations running - and I don't know when will this stop.

Now the thing is that I want -- is based on the user input like I have said above if the user input Employee ID as 101, 253 , I can get the output as stated above by building the analytical app.

 

And I am not able to figure out the logic for that like to contain the iterations and run for only those which the user has given in the input, if you have understood my query you can try once.

 

I just want that when the user provide the input(Employee ID either single or multiple) and based on those input only the workflow should iterate for only those given input rather than iterating on the whole dataset of 100,000 rows among themselves so that instead of the endless loops I can get my desired output easily. 

danilang
19 - Altair
19 - Altair

Hi @P007 

 

The issue with the original and the suggestion from @Pang_Hee_Choy is that both of them have a pseudo-cross join in them that attempts to match all employees(minus the ones with no supervisors) with all managers, returning all the employees for  all the managers at each iteration. It essentially does a join with all 100000 records on both sides.  The original just returns the entire hierarchy and @Pang_Hee_Choy's version first builds the hierarchy and then attempts to search this for the manager and employee information.

 

In order to solve this in a reasonable time, you need to avoid that cross join.  In this version, the complete list is only joined to the current manager list.  With your sample data and passing in only 101 and 253, the first iteration joins the entire list to only the 2 managers, returning only 6 records, which are the direct reports of 101 and 253.  These 6 records are then used as managers in the second iteration.  At each iteration, only the targeted managers are joined with the initial list, thus reducing the amount of time required for each iteration. 

 

 

danilang_0-1634998130744.png

 

The amount of time this version will take is dependent of the number of managers that you initially include in the supervisor list and the depth of the hierarchy for each of those managers.  Obviously, if you include all the supervisors it will take as long as the cross join version.  

 

Dan

 

Labels