Crosstab
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
Looking for some help!
I have an input excel file with contact information. A contact appears many times and can have multiple roles. I need to make the list of reps distinct and then need to flatten the multiple roles into single row per contact. Need to have all the roles separated by a comma in one row per contact. I tried this logic using group by and cross tab tool but not sure about the column headers and values for new columns field in crosstab tool. Any help on this would be great. Thank you in advance!
Input
Name Phone Email Role
Bob Smith 1234 bob.smith@abc.com Manager
Bob Smith 1234 bob.smith@abc.com Branch Head
Bob Smith 1234 bob.smith@abc.com Market Head
David Williams 5678 david.williams@xyz.com Assistant
David Williams 5678 david.williams@xyz.com Market Head
Desired Output
Name Phone Email Role(New Column)
Bob Smith 1234 bob.smith@abc.com Manager,Branch Head,Market Head
David Williams 5678 david.williams@xyz.com Assistant,Market Head
Solved! Go to Solution.
- Labels:
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi, @aparna0208
No worries.
Looks like your actual case is a little more complex than the sample.
Assuming that you still only want Name, Phone, Email, and Role in the output then try the below (gave you two options).
If you need all the other fields to come into play then we might need to insert Regex at the end to remove duplicate roles. So let us know if we need to go there!
The workflow is attached.
Cheers!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you so much for patiently answering all my questions!
This is actually very close but role is kind of repeated in that column. I have to update this final output into few fields. So the roles will go into a field in database called Ttile and should have only one unique role. For example: Branch Head, Manager is mapped to a contact called Bob Smith more than once like
Bob Smith Branch Head
Bob Smith Manager
Bob Smith Manager
Bob Smith Branch Head
So output should be like below
Bob Smith Branch Head,Manager
One of my colleague suggested trying group by and crosstab and he got the output as seen in attached snapshot. I'm trying your logic and works fine for group by but the final output is bit different so not sure how to use the crosstab tool
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @aparna0208 amended workflow let me know what you think?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Awesome:) Both solutions worked @JosephSerpis @RobertOdera
Once again a big thank you for patiently trying out different solutions till I got what I expected:)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- « Previous
-
- 1
- 2
- Next »