Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Crosstab

aparna0208
8 - Asteroid

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

15 REPLIES 15
JosephSerpis
17 - Castor
17 - Castor

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
13 - Pulsar

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!

 

RobertOdera_0-1592506092719.png

The workflow is attached.

Cheers!

aparna0208
8 - Asteroid

 

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
17 - Castor
17 - Castor

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

aparna0208
8 - Asteroid

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
13 - Pulsar

You're most welcome @aparna0208 !

Got questions? Get Answers! This Community rocks!

Cheers.

Labels