Searching a value based on Manager ID in same file.

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

Developer Tools

Accepted answers

MSalvage

@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

sr49ers Solution.yxmd

All comments

MSalvage

@SR,

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

Attached is a sample solution.

Best,

MSalvage

sr49ers Solution.yxmd

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335