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

How to Convert List to Columns

mjsestak
5 - Atom

I am new to the community and am trying to get up to speed on using the tool.  My first real world data manipulation task is to convert a list of addresses into a table format.  My data is in the following list format with each line being a new field in an excel spreadsheet:

 

Name 1

Address 1

City, State 1

Notes 1

Blank Line

Name 2

Address 2

City, State 2

Blank Line

Name 3

Address 3

City, State 3

Notes 3

...

 

I was hoping to use Alteryx to clean up the data, but I am struggling to find a way to get the info into the format I want, especially since some of the records have three lines and others have four.

Name 1 - Address 1 - City, State 1 - Notes 1

Name 2 - Address 2 - City, State 2

Name 3 - Address 3 - City, State 3 - Notes 3

 

Any help would be greatly appreciated,

Michael

5 REPLIES 5
DultonM
11 - Bolide

Hi @mjsestak!

 

Welcome to the Alteryx Community! I was able to get Alteryx to produce your desired output!

 

Whenever you are wanting to take data from rows and spread it across columns, the Cross Tab tool is the place to go. But in order to use the Cross Tab in this particular situation, I had to first create two additional columns using Multi-Row tools: one to denote each "group number" (i.e. Name 1, Address 1, City, State 1, & Notes 1 all belong to the same group) and one to denote each "column number" in a group (i.e. Name 1 is the first column, Address 1 is the second column, etc.). Once I had that, I could cross tab, using the column number as the headers and grouping on the group number.

 

I attached a workflow so you have all the fine details. This is just one of possibly several ways to accomplish this. My approach assumes 1 blank row separates each group. It also assumes that Name, Address & City, State are always present, in that order. The Multi-Row tools make the workflow function whether or not the Notes lines are present. Let me know if you have any questions!

JohnJPS
15 - Aurora

The regular formula tool can do some quality parsing using "GetWord" and "CountWords," in this particular situation where the last "word" is basically the row count.  Thereafter my solution is essentially the same as @DultonM's.  (Or... maybe I royally misunderstood that and the "blank line" is delimiter, and there is no built in counter... in which case ignore this!)

mjsestak
5 - Atom

This looks really good, except I am unable to open the workflow that you attached.  It says that it was created by a newer version and it's unable to open.  I have 10.1.6 so I am not sure why it isn't able to read it.

 

Thanks for the quick turnaround and help!!!

Michael

DultonM
11 - Bolide

Glad to help! Alteryx's latest version is 10.6.8. I built my workflow in version 10.5. I was able to open @JohnJPS's workflow, so I'm guessing he built his in 10.5 as well. I went ahead and reattached both our workflows (hope you don't mind John) after editing the XML to be version 10.0. Hopefully now you'll be able to open them!

mjsestak
5 - Atom

Thanks so much for your help!!!!  I really appreciate it.

 

Michael

Labels