Unique Office Address Support

[pbresney]

I have a list of offices and their corresponding manager, however there is no specific flag for a manager. I have a second set of data that contains all of the address that are linked to the employee. Since employees often work at multiple locations, they have multiple locations address data associated with their profile. After I join these two data sets, I get far too much data, since it brings in all of the individual employees addresses that are linked to multiple locations. How can I ensure I only keep the Addresses based on the first sheet? SO employee A works for building C, D and E but is the manager for D, how do I ensure I only keep the address for D for that employee. This dataset contains thousands of employees and offices so I cannot do it manually.

[Pilsner]

Hello @pbresney ,

Based on your post it sounds like you have two datasets. One corresponding to Offices' and their corresponding managers, and the other dataset corresponding to all employees. My understanding is that the second data set may have multiple rows of information per employee, each row being made unique, as per the combination of employee and address. I have made the assumption that this address column is the address of one of the buildings (offices) that they work in. If this is the case the problem couple be tackled in a couple of ways.

You could either add more criteria to the join tool, or do a combination of filtering/ unique values, after the initial join that you have already created. I have attached a workflow below with some dummy data I created to help demonstrate my thoughts.

I may have oversimplified the problem, if this is the case, please could you provide some sample or dummy data and an ideal outcome, and id be happy to assist further.

Regards - Pilsner 

[chuckleswk]

It would be helpful to see what the two inputs look like and what the desired output looks like... but if I had to guess, you're looking for something like this: 

You can achieve this using the following attached workflow:

 

[pbresney]

Here is the sample Data. To put it more clearly, I need to make sure the line with the correct office from Data set 1 remains from the data in data set 2 so I can get the correct address id. 

[pbresney]

Here is the sample Data. To put it more clearly, I need to make sure the line with the correct office from Data set 1 remains from the data in data set 2 so I can get the correct address id. 

[chuckleswk · Accepted solution]

I think that I have what you're looking for, but it would still be helpful to have what you expect the output to look like.