Crosstab

[aparna0208]

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

[JosephSerpis]

It an alteryx package it's essentially a zip file with the workflow and the data. I've attached the workflow you will just need to change the input when you try to run it.

[RobertOdera · Accepted solution]

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!

[aparna0208]

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

[JosephSerpis · Accepted solution]

Hi @aparna0208 amended workflow let me know what you think?

[aparna0208]

Awesome:) Both solutions worked @JosephSerpis @RobertOdera 

Once again a big thank you for patiently trying out different solutions till I got what I expected:)

[RobertOdera]

You're most welcome @aparna0208 !

Got questions? Get Answers! This Community rocks!

Cheers.