Lookup Function in Spotfire

[Damilola Adeyemi]

I want to return each Manager's Email address when the Manager's Name is equal to their Name on the Name field - return their email address.

The Manager's Name is a CalculatedColumn but I have the Name and Email Address of everyone in the dataset

 

This was my expression but it returning anyone that report to those managers

 

case

when [name] = [Manage Name] then [EmailAddress]

ELSE NULL

END

 

I need a calculated column for

 

 

 

Manager Name

 

 

 

and

 

 

 

Manager Email

[Aditya Singh 3]

Can you share some sample data

[Khushboo Rabadia]

You can try this using calculated columns. Suppose the dataset is same as you have attached with Manager column contents in the same format then you can use RXExtract function to extract Manager Name like

2. Data > Add calculated column...

Column name: ManagerName

Expression: RXExtract([Manager],"\w+\s\w*",1)Then you can create other calculated columns to get Manager Email. Like concatenate all manager names you extracted above

3. Data > Add calculated column...

Column name: ManagerList

Expression: uniqueConcatenate([ManagerName])Now create list ofManagers name, email length and email that you get from Email column if FullName is part of ManagerList created above

4. Data > Add calculated column...

Column name: EmailList

Expression: UniqueConcatenate(case when Find([Full Name],[ManagerList])0 then Concatenate([Full Name],Len(),) end)Once the Email list is ready, search for ManagerName in EmailList column, if found then extract the email using substring method. Using Find function you can get the position of string matched and Mid function will help to extract the length of manager email

5. Data > Add calculated column...

Column name: ManagerEmail

Expression: case when Find([ManagerName],[EmailList])0 then Substring([EmailList],Find([ManagerName],[EmailList]) + Len([ManagerName]) + 2,Integer(Mid([EmailList],Find([ManagerName],[EmailList]) + Len([ManagerName]),2))) end