community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
Alteryx Gallery is experiencing a problem in which system emails are not being sent out. As a result, if you are attempting to sign up for a new account, you may be unable to verify your email address. We are working to solve this as soon as possible and will remove this notice once resolved.
SOLVED

Unique rows to columns issue

Highlighted
Meteoroid

Hi everyone,

 

I have a dataset that has two fields. One field is my column heads and the other is the values. I am attempting to crosstab them so that I have a functioning dataset. The field names repeat and I am not quite sure how to convert this.

 

The dataset is the parsed html/xml from a kml file to add some context.

 

Final fields should be: ZIP_CODE | STATE | POP2016 | POP16_SQMI | SQMI | PO_NAME

 

Any help is appreciated and please let me know if you have any questions in case I need to clarify.

 

Current:

FieldsValues
ZIP_CODE1
STATEAK
POP2016-99
POP16_SQMI-99
SQMI16279.47
PO_NAMEN Dillingham Census Area
ZIP_CODE2
STATEAK
POP2016-99
POP16_SQMI-99
SQMI95704.75
PO_NAMEYukon Flats Nat Wildlife
ZIP_CODE3
STATEAK
POP2016-99
POP16_SQMI-99
SQMI14491.7
PO_NAMEAlaska Peninsula NWR
ZIP_CODE4
STATEAK
POP2016-99
POP16_SQMI-99
SQMI6568.13
PO_NAMEW Kenai Peninsula Boroug
ZIP_CODE5
STATEAK
POP2016-99
POP16_SQMI-99
SQMI3713.14
PO_NAMEN Lake and Peninsula Bor
ZIP_CODE6
STATEAK
POP2016-99
POP16_SQMI-99
SQMI16293.2
PO_NAMEMatanuska-Sustina Bor
ZIP_CODE7
STATEAK
POP2016-99
POP16_SQMI-99
SQMI65388.41
PO_NAMESoutherly North Slope Bo
ZIP_CODE8
STATEAK
POP2016-99
POP16_SQMI-99
SQMI832.76
PO_NAMEAleutians West
ZIP_CODE9
STATEAK
POP2016-99
POP16_SQMI-99
SQMI2769.67
PO_NAMELake Clark National Pres
ZIP_CODE10
STATEAK
POP2016-99
POP16_SQMI-99
SQMI5215.68
PO_NAMEYukon Delta Wilderness
Asteroid

I was able to use the crosstab, split to columns, and join multiple tools to get it to come out the way I think you wanted it.

 

Crosstab Start.JPGStart of WorkflowCrosstab End.JPGEnd of Workflow

I feel like there is a more efficient way than what I have created, but this should get the job done. I have attached my workflow so you can try it yourself.

 

Let me know if that helps!

-AD

Alteryx Certified Partner

Hey @ADerbak

 

If you're looking for a simpler method, you can use the multirow formula to add a recordID to each "group" of records and then crosstab back:

 

mr.PNG

Asteroid
I knew there was a simpler way! Thank you @LordNeilLord!
Meteoroid

Thanks for this! #GODLIKE @LordNeilLord @ADerbak

Labels