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

transpose and crosstab

Brad1
11 - Bolide

I trying to get this:

 

Acct NumberType
111A
111B
222H
222I
222J
222K
333M
444A
444X
444Y
444Z

 

To look like this:

 

Acct NumberType1Type2Type3Type4Type5Type6Type7
111AB     
222HIJK   
333M      
444AXYZ   

 

Can it be done?  Any ideas?  Thanks.

7 REPLIES 7
tom_montpool
12 - Quasar

Definitely possible.

You'll need to add an additional field for the Crosstab tool to use as the field headings.

There are several ways to do it -- I've included one.

Joe_Mako
12 - Quasar

How about the attached?

 

crosstab.png

- Multi-Row Formula set to Group on Acct Number to get an incrementing Count for each record 

- Cross tab to use that Count as the field name

- Dynamic Rename to as the prefix

Brad1
11 - Bolide

Wow!!!  Thank you soo much!!!  It works!   I owe you a beer at Inspire.

Brad1
11 - Bolide

Thanks Joe.  I'll check yours out as well.  I greatly appreciate everyone's time.  I'm new...

tom_montpool
12 - Quasar

If you're new to Alteryx, you could set yourself a challenge to find alternate ways to add the incrementing field that both @Joe_Mako and I did with the Multi-Row formula tool.

 

Joe also cleaned things up for you and added the prefix to the final field name.

MarqueeCrew
20 - Arcturus
20 - Arcturus

@Brad1,

 

I'm late to the party, but wanted to show you an alternative (laboratory) way to solve this problem.  I'm going to use a "Make Columns" tool.  With it, I can configure it for 7 columns and arrange the data how you want it (Horizontally).  The trick here is to rename the output fields (Column_1_Type) to your preference.  I did that with a regular expression:

Regex_Replace([_CurrentField_],"Column_(\d+)_(.*)",'$2$1')

Capture2.PNG

As I was walking my dog I thought of this alternative approach to the problem.  Because of it's unconventional approach, I thought that I'd post it.  I named it, transpose and crosstab without transpose and crosstab in your honor.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
tom_montpool
12 - Quasar

@MarqueeCrew -- a fantastic way to show the flexibility of Alteryx and how inspiration can strike at the oddest times.

Labels