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

Splitting cells with multiple line items in each cell (vertical and horizontal)

Processcreator
7 - Meteor

Ok this one is a bit tricky for me so appreciate any help in advanced. I am working with a data set that unfortunately isn't very user friendly. It essentially has multiple lines in each cell (though not all). I am trying to split them but only when there are multiple lines. Additionally, I want to text to columns so that the number and state are in seperate columns. I realize this is multiple tools / steps but have tried so many different ways and couldn't get it to work I thought I would provide the starting point and desired result.

 

Raw Data

 

 

raw.jpg

 

 

 

 

 

 

 

 

Desired Result

Desired Result.jpg

 

7 REPLIES 7
MichalM
Alteryx
Alteryx

The most straightforward way would be to use the Text to Columns tool number of times in a sequence.

 

First, you'd want to split your data into rows rather than columns on an embedded new line characters which are present across all three of those columns - you'll use \n to identify this delimiter.

 

ttc1.png

 

Once you have all the rows in your data, you will use the same tool in the Split to columns configuration to split each of the on the hyphen.

 

ttc2.png

 

ttc.png

mmenth
11 - Bolide

Hey @Processcreator 

 

Try the attached. I tried to make it in a way where it will be flexible if you add more columns with different date names.

 

Best,

Megan

Processcreator
7 - Meteor

Hi Mmenth,

I received the following error 

This workflow was created by a more recent version of Alteryx, and may contain tools or functionality not present in this version. Alteryx does not support using an earlier version of Alteryx to open a workflow created with a newer version. For best results, download the latest version of Alteryx.

 

any way to fix this?

mmenth
11 - Bolide

Hmmm I haven't had luck saving as a previous version with Alteryx, but I can try it. What version are you using? Any chance you can update to 2019.1?

 

Best,

mmenth

MichalM
Alteryx
Alteryx

Try the attached @Processcreator 

 

It will work with 11.5 and newer.

estherb47
15 - Aurora
15 - Aurora

Hi @Processcreator 

 

Here is something that should work for you, even as you add more columns to your data. It's dynamic because it parses into rows, then filters on what are numbers and what aren't, rebuilds the table, and joins back into position.

Let me know if this works.

Cheers!

Esther

image.png

Processcreator
7 - Meteor

This worked great- thank you!

Labels