Create Email List from Email Format Possibilities

[jkgeorge89]

Hello community!

I've been working in Alteryx for a few months now and have searched online, videos, previous threads for help with a workflow. Hopefully someone will be able to either offer a suggestion or help me with the issue. 

Objective: take a list of contacts provided (~40k) and dynamically create their email address based on an 'email format'

Data: I have a large data set from numerous different companies which all provide their 'email format'. The issue is there are about 30-50 different combinations of 'first name', 'last name', 'middle name', 'initials', and 'punctuation (".","_","-") that are used within these email formats. Also, the data has some issues with the 'parsed' names and so I've had to rely on the 'full name' and have Alteryx parse it - no problem there. 

Issue: I'm trying to figure out how to read the text 'firstname' and have it pull in the column named 'First Name'. As an example, the data attached first email format is 'first.last@company.com' so if we were trying to write a formula it would be something like:  [First Name], ".", [Last Name].

Workflow: so my thought process was to create a column that would replace the string with the column names associated with them and leave in any punctuation as some formats have them and some don't. The last step would be to read [First Name].[Last Name] (which are the actual header names) and pull in the values associated with the records. 

But this is where I'm having some serious difficulty and would like anyone's thoughts. I also know with any problem there are MULTIPLE approaches and solutions, so if there is a tool or simpler approach please don't hesitate to suggest another avenue for solving the problem. I've attached my workflow with some sample data (obviously the contact records can not be shared so there are about 10 of them in a text input to kick off the workflow).

[cplewis90 · Accepted solution]

Hey @jkgeorge89,

I think you are starting down the right path! You would need to convert the email format into an ALteryx formula expression. So "first name.last name" would turn into "[First Name]+'.'+[Last Name]". 

Once you have that you would want to create a macro that accepts your data and batches through the different email formulas. When you pass each formula as a parameter you would have it update the formula tool.

I attached the zipped workflow that took your work and added the macro that does this.

[jkgeorge89]

@cplewis90 

Thanks you so much for your thoughts on the workflow. I had a feeling that I'd have to go down a macro path as the number of possible formulas is too much to try to do with the Formula tool. I'll have to take a deeper dive into the macro in the background and try to understand the logic behind it. But really do appreciate your help. Hopefully one day soon I'll be able to pass it along. This community is AMAZING. 

[cplewis90]

If you have any questions on it feel free to ask. I will be more than happy to help