How can you find a Value in different column and then return it to a seperate row

[Gianfranco Vennettilli]

Name

Unique ID

Reference ID

Reference Name

 

 

John

12345

 

 

 

 

Carl

23456

12345

 

 

 

Steve

34567

 

 

 

 

Bob

45678

23456

 

 

 

Jim

56789

 

 

 

 

Joseph

67890

78901

 

 

 

Louis

78901

56789

 

 

 

Peter

89012

90123

 

 

 

Frank

90123

 

 

 

 

 

 

 

 

This is the best sample Dataset I could come up with. So in this example, we have employees' names (John, Carl, Steve, etc.) and then their employee ID number. They are not in and specific order. Then Reference ID is the ID number of the employee who recommended them to the company.

 

What I am trying to do is create a calculated field (Reference Name) that will, where there is a reference ID, contain the name of the employee who that reference ID corresponds to.

I have tried a couple of different things, but have not had really any success. Any help would be greatly appreciated.

[Khushboo Rabadia]

Same approach mentioned in this article can be helpful in your case as well:

https://community.spotfire.com/questions/use-over-function-find-specific-value-another-column