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

Multi-Field Text to Columns (Rows)

EYamada
7 - Meteor

Hi Everyone

 

I have a dataset where the data in multiple fields contains delimiters; see below.

 

NameIDRecord ID
John Smith|John Smith1|231|2
Jane Smith|Jane Smith2|503|4

 

Anyone have any thoughts how to split all the fields into multiple rows?  Please see below for the output I am trying to achieve.

 

NameIDRecord ID
John Smith11
John Smith232
Jane Smith23
Jane Smith504

 

Please let me know.  Thank you!

 

EY

6 REPLIES 6
Joe_Mako
12 - Quasar

How about the attached?

The workflow will add a ReocrdID (uniquely identify source rows), Transpose to a Name-Value pair for every cell, add a NameID (uniquely identify fields), Summarize Group by NameID,Name for use in renaming the fields later, split the Value field on the | deliminator into rows, add a ValueID (uniquely identify each value and sequence in a record's cell), cross tab into normal structure, deselect unnecessary field, and rename fields.

 

You might notice that I do not let the Cross Tab tool use the Name field to name the fields. This is because it will change the order of the fields to alphabetical and it changes the field names, replacing some characters with an underscore.

 

split.png

zpruitt
5 - Atom

Hey, Joe

 

I made a slight change to your answer, only because in my case it was staggering the rows (all of the data of the first column would populate, with the remaining columns showing as null until the first column finished). To fix this I edited the 2nd formula (ValueID: [Row-1:ValueID]+1) to IF [Row-1:Name]==[Name] THEN [Row-1:ValueID] +1 ELSE 1 ENDIF

amc1794
6 - Meteoroid

Hi Joe_Mako,

 

Is there any way to make this work when there are other additional columns of data which you want to be duplicated in the additional rows which have been inserted? I have adjusted EYamada's example below to explain.

 

Before

 

NameIDRecord IDCityCountry
John Smith|John Smith1|231|2New YorkUSA
Jane Smith|Jane Smith2|503|4LondonUK

 

After

 

NameIDRecord IDCityCountry
John Smith11New YorkUSA
John Smith232New YorkUSA
Jane Smith23LondonUK
Jane Smith504LondonUK

 

Really appreciate your help.

Joe_Mako
12 - Quasar

The attached macro is similar to the above workflow, but with some improvements and the added capability you are looking for.

split macro.png

- Field Info to get the field names, so the fields are in the same order on output as input.

- After creating the ValueID, tho Summarize tools get the Max ValueID per Record, and per RecordID-FieldID combination, and joining on both RecordID and Max ValueID to find the cells that need to be filled down.

- Generate Rows starts at ValueID+1 so there is no duplication.

- Everything else is the same logic as detailed above.

danescruz
5 - Atom

Hi Joe,

 

I am using this macro and its amazing!

Thank you for this solution! 

 

I have 1 question, I have some columns where the data is cut after the transformation.

For example, the word "Global" in 1 column becomes "Glob" (missing al)

However, other columns that have greater content like paragraphs don't get cut.

I noticed most of them are from my first few columns.

 

What should I do?

Tanujit
5 - Atom

Awesome! many thanks for this solution. It helped me in similar kind of case.

Labels