Damilola Adeyemi Posted May 20, 2019 Share Posted May 20, 2019 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 Link to comment Share on other sites More sharing options...
Aditya Singh 3 Posted May 21, 2019 Share Posted May 21, 2019 Can you share some sample data Link to comment Share on other sites More sharing options...
Khushboo Rabadia Posted May 22, 2019 Share Posted May 22, 2019 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now