Hi all,
I am trying to achieve the below (dummy data) via the transpose tool. Essentially, we want one row per contact name/address. Individuals can provide up to three contacts (not all will have three - would prefer not to have null rows).
My issue with a regular transpose is that I only have one 'name' column and one 'value column' -- whereas I need two each to capture name and email address.
Appreciate any solutions.
Input:
Output:
I figured it out
What you want to do is to come up with one common way to look at all names and email addresses, as well as a way to have a unique ID for each contact.
My workflow:
- first transposes grouped by Name, ID, Program, Region, and Location. This gives you a row for each contact name and contact email.
- Next I used regex to pull out the number of the contact (e.g. contact 1, contact 2, or contact 3 becomes 1, 2, or 3) to know which contact the row is referencing.
- Next I used the formula tool to turn "Contact 1 Name" or "Contact 1 Email" into "Name" or "Email", which will allow me to cross tab it.
- Next I used cross tab grouped by the same fields as original - Name, ID, Program, Region, and Location - but ALSO by the unique contact number (1, 2, or 3)
- Finally i use a select tool to rename the contact name and contact email fields, as well as remove contact number, which was only used to be able to crosstab it.
I hope this works for you.
Edit: This is the result
Bummer the file didn't work. Can you try the attached workflow package on this comment? Maybe the issue had to do with not having relative references.
Screenshots are below
Transpose configuration:
Regex configuration:
For the regular expression itself, the "\<\w+\>" just means a word, so it picks up Contact. Then the "\s" means space, so it picks up the space after Contact. Then the (\d) is looking for a digit character, and the parentheses puts it in a marked group, which is what we want to pull out from the string. Then after that there is another "\s" for the space after Contact 1, Contact 2, or Contact 3, then another "\<\w+\>" that describes Name or Email.
In hindsight, we could have made that last "\<\w+\>" a marked group too and parsed it out into a new column, instead of doing it with the formula tool next... But both ways work.
Formula tool configuration:
The regex I used here is a little different, but still accomplishes the same thing as if we used the same regex from the previous step. I use "$3" to represent the 3rd marked group, which in this case is either "Name" or "Email"
Crosstab configuration:
Just group by the fields that are not Name2 (either Name or Email) or Value (The name of the contact, or that contact's email address). Make the column headers Name2 so that the email addresses are now in the Email column while contact names are in Name2. Values for New columns come from Value.
Select tool configuration:
I dropped the contact number field, since you didnt want that in your output, and renamed Name2 to Contact Name and Email to Contact Email.
I hope this helps you to recreate it.
