We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

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

Pivoting and Parsing data

mcmahond
6 - Meteoroid

I have a ton of data that is set up similarly to the Sample Input attached.  I am trying to take the Sample Input and make it look like the Sample Output.  I have tried pivoting, transposing, RegEx, and a combination of all.  I have been searching the discussion boards for days and finally decided to post.  This is my first post ever and will not be my last.  The Community here is amazing and I have learned so much from everyone the short time I have been using Alteryx.  Any help would be greatly appreciated.

4 REPLIES 4
apathetichell
18 - Pollux

this is yucky data. You can start by using a multi-row formula to create an index. You use:

if [Row-1:F1]=null() then [Row-1:index]+1 else [Row-1:index] endif (with values that don't exist set to 0) and create a new index.

you use tile tool (unique value/index) - to get your number of fields.

you filter for nulls.

you crosstab (f1 becomes your values, tile secquence number is your column header and tile number is your primary key).

 

now comes the annoying part. addresses are particularly annoying to divide up. I'd recommend using something like:

regex_replace([2],"^(.*),(.*),","$1") for the street column

I'd recommend something like left(replace([2],[street column]+", ",""),2) for the state

you can then try something simlair to extract zip. you can also try regex_replace([street column],"^(.*, \w{2} )(.*)","$2") to capture your zip.

TimN
13 - Pulsar

I agree that Regex is the way to go for parsing.  My regex is not good so I did it without.  There has to be a better way but here's my attempt.

mcmahond
6 - Meteoroid

Thank you for this.  This is awesome!  I am going to try this solution and work through the RegEx and learn more.  I appreciate the help.

mcmahond
6 - Meteoroid

I appreciate the help on this one.  This solution is more my speed because I am not great with RegEx yet either. Thank you for taking the time to go through this.  This is awesome as well.

Labels