Alteryx designer Discussions

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

Turn fields into different columns

Highlighted
Meteoroid

Hello,

 

I have a dataset with the following characteristics:

 

Row #1: Transaction A - Location New York

Row #2: Transaction B - Location Seattle

Row #3: Transaction A - Location Washington

Row #4: Transaction B - Location Detroit

...

 

I would like my dataset to look as follows:

 

Row #1 - Transaction A - Location 1 New York - Location 2 Washington

Row #2 - Transaction B - Location 1 Seattle - Location 2 Detroit

 

Basically, I would like to move from two columns (Transaction and Location), to three columns (Transaction, Location 1 and Location 2). What is the easiest way to do this?

 

Thank you in advance for your help!

 

Best

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Use Multi-Row Formula tool to create a unique location number grouped by [Transaction].

 

Use the Formula tool to create the row headers.

 

Then use the Crosstab tool to spread the data.

Highlighted
Meteoroid

Thanks @michael_treadwell!

Highlighted
Meteoroid

Michael,

 

When I run your flow, I get as many columns as the number of different locations I have in the data. However, I would like to have as many columns as the number of times a transaction appears in the database (e.g. assuming the largest transaction repeats itself five times, five "location" columns would be created), even if the location is the same in all columns. 

 

Do you - or anyone reading this - see a way to solve my issue?

 

Thank you in advance

Highlighted
Moderator
Moderator

Hi @zemariajudice,

 

Could you possibly give an example of what you would like the output to look like?

 

Cheers,

Sophia Fraticelli
Premium Support Advisor
Alteryx, Inc.
Bolide

@SophiaF totally going down a rabbit trail, but you have the best avatar since Mark's rolling BB-8!

@thizviz
Labels