self join loop

Hi Knime Users,

I have this dataset. selfjoin loop.xlsx (1.6 MB)

I need to build a reporting hierarchy, ie. Agents report to managers, managers report to senior managers etc, which is depicted with the column “rolename”. L1 are the base (Agent level), L2 would be their managers.

I am able to build this by using Joiner nodes one after the other, but I would like to implement this using some sort of loop. Not sure how. My initial workflow was not added as this is the first step in the process.

Any help would be awesome!

Thanks,
Partha

Hello @parthak,

I have taken a look at you input file but column headers are missing and I’m having hard time interpreting your data. What is connection (column) between Agent and Manager? Also how should output look like? Can you share smaller (dummy) input data and desired output? Then should be easier to understand and propose solution/approach :wink:

Br,
Ivan

Hi @ipazin ,

Looks like I forgot to check the column headers check box in the export node.

To elaborate:

I am attaching 2 input files.

  1. Agent Data - Agent Data.xlsx (285.2 KB)
  2. Manager Data - Manager Data.xlsx (562.4 KB)

The idea is to link the Manager ID column in the Agent data to the Employee ID column in the Manager data, n number of times till there is no more match.

My problem is that, I built a workflow which uses 4-5 joiner nodes, but it is a manual process. The input data and the levels of join changes constantly and if it changes I would have to go in and add/remove a joiner node to address the issue.

I was wondering if there was a way to build a loop which exits if there is no join.

I have attached an output file for better understanding.

Output file - Final Output Data.xlsx (450.9 KB)

In the output file, if you scroll all the way to the right you can see that there is no data in the last 4 columns. So I need to exit the loop at the managerid (#5) column.

My workflow is still stuck at this step and I’m looking for any help.

Hope this is useful for you to think of something.

Thanks.

Regards,
Partha

Maybe you can group the agents to figure out if 4 or 5 joins are necessary and start from your grouping result?

Hello @parthak,

give it a try with Recursive Loop. You can stop when join provides no match or based on variable.

Take a look at this example workflow:

Br,
Ivan

1 Like