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

Group and transpose part of the data table

anhdo92
8 - Asteroid

Hi everyone,

 

Since I'm a beginner, this is probably a very simple question - but searching for ideas to solve my problem didn't yield any useful results. I hope this post can help me find a solution from the community

 

I have this list of contacts (file attached) in which some of them look almost like duplicates. They share the same ID, Names, Address, Post code, but not in Component, and Sequence. I want to group all contacts that share similarities and then transpose other rows such as Facsimile, Phone, Email Address, and Contact into Columns.

Basically the data looks like this:

 
 

Contact list.PNG

 

And I want to transform the whole dataset into something like this: 

Expected OutputExpected Output

 

Any idea is very much appreciated!
Thanks

2 REPLIES 2
TonyA
Alteryx Alumni (Retired)

I've attached a workflow that I think does what you want. It's a little more complex than I would like, but I couldn't think of a better way to handle the mix of different types of data in the "Email, Swift, BIC,Chips etc" column. Also, Crosstab sorts the rows and columns and I had to correct the final output for that by temporarily adding a row counter and a prefix to all the column names.

 

I did get a slightly different result from you for the company with ID 8222222. In the output you only had rows for that company with a Component value of FX, but the input file has values of FX and UK. It looks like you did want to retain both since the company with ID 6345789 has both UK and FX in the output and all other grouping columns are identical. Am I missing something?

 

2019-10-02_22-03-16.png

anhdo92
8 - Asteroid

@TonyA  Fantastic!! This looks way more efficient than my workflow. Yes I wanted to retain both values. Thanks for correcting that. 
And thank you very much for your help. This is exactly what I'm looking for.!



Labels