transpose and crosstab

[Brad1]

I trying to get this:

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

To look like this:

Acct Number	Type1	Type2	Type3	Type4	Type5	Type6	Type7
111	A	B
222	H	I	J	K
333	M
444	A	X	Y	Z

Can it be done?  Any ideas?  Thanks.

[tom_montpool · Accepted solution]

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]

How about the attached?

- 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 · Accepted solution]

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

[Brad1]

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

[tom_montpool]

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]

@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')

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

[tom_montpool]

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