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

Unique rows to columns issue

lesliefenwick
6 - 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
4 REPLIES 4
ADerbak
11 - Bolide

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.

 

Start of WorkflowStart of WorkflowEnd of WorkflowEnd 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

LordNeilLord
15 - Aurora

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

ADerbak
11 - Bolide
I knew there was a simpler way! Thank you @LordNeilLord!
lesliefenwick
6 - Meteoroid

Thanks for this! #GODLIKE @LordNeilLord @ADerbak

Labels