Searching a value based on Manager ID in same file.

[SR]

Hi All,

I am trying to search manager location based on the manager id in the below table. the goal is to input Austin in Row 1 , NY in Row 2. how can i achieve this .

Emp ID	Manager ID	Location	Manager Location	row
123	899			1
899	122	Austin		2
122	453	NY		3

Thanks

SR

[MSalvage]

@SR,

I would join your data to itself, linking manager ID and Emp ID.

Attached is a sample solution.

Best,

MSalvage

[SR]

thanks for the reply. I tried your method but my data set has duplicates emp id which for reason is causing an Cartesian join .

how can i avoid that.

or let me rephrase my problem.. Basically some emp if will have location null . however their manager will always have a location populated. so where ever an emp has null location, i want it to search the manager's location and input that value. so for emp 123, it should search manager 444's location which is Austin. Similarly , for 544 it should input NY.

Emp Id	Manager ID	Location
122	444	Austin
123	444
444	999	Chicago
544	999
999	X	NY

Thanks

SR

[MSalvage · Accepted solution]

@SR,

I think using my previous solution should still work. I would just utilize the new "Manager Location" field to populate the Null Employee locations. Just use a Formula tool to update the Location field with the following:

IF IsNull([Location]) Then [Manager Location]

ELSE [Location]

ENDIF

As far as the duplicates go I would use a unique tool on the manager side data stream before the join.

Best,

MSalvage

[SR]

this worked. thank you for your help!