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

Pivoting the data

Pav
7 - Meteor

Hi,

 

I've a dataset that looks like this(demo only, I've more columns in it)

TitleMrMrs
NameJohnAlice
SurnameAdamsAdams
Address3z4z
Phone123456

What I want to do is to pivot the data in a way that my new headers are the values from first column and the rest follows the suit. Exactly how it looks below.

 

TitleNameSurnameAddressPhone
MrJohnAdams3z123
MrsAliceAdams4z456

I tried using Cross Tab to do this but it works partially as it only allows me to select one column for values hence I lose all the info in the third column.

Is there a way to do this, bearing in mind that the dataset is much larger and there are more columns. 

 

Thanks

9 REPLIES 9
JohnJPS
15 - Aurora

Hi Pav,

Try a Transpose first, Key Field your first column, all other fields as Data Fields.  Then do the Cross Tab, again with your first column as the only Group Data field, and then "Name" as New Column header and "Value" as "Values.

Hope that helps!

Pav
7 - Meteor

Hi John,

 

Unfortunately this didn't work for me, it produces the exact same output as input i.e. the table looks exactly the same prior to using the transponse and cross tab tools. 

JohnJPS
15 - Aurora

That's my fault for going off the top of my head!  I've attached a workflow that should get the job done; same idea but includes a Record ID as well.

 

JohnJPS
15 - Aurora

PS, and a Dynamic Rename can shift the first row of data into the column headers if desired as well:

Capture.JPG

 

JohnJPS
15 - Aurora

This question is actually fairly common so I went ahead and created a macro that "totally transposes" the data, all rows to columns and vice versa; with the option to do that shift into column headers.  Hopefully you or anyone else might find it handy.  (Attached).

 

EDIT: altered to use user-specified column name for the input column to transpose into column headers.

 

Pav
7 - Meteor

This is fantastic, thank you John! 

JohnJPS
15 - Aurora

No problem!  PS, I also realized that, depending on the input column names, simply grabbing the first one (in the macro) isn't reliable, since they could get reoardered during Transpose/Cross-Tab.

 

I therefore tweaked the macro to allow the users to specify an input column, if desired, that will contain the output column names.

 

Rather than re-attach it here, I edited the original post above to include the corrected version of the macro.

 

Enjoy.

 

dlesny
8 - Asteroid

Hi Thank you for this solution. Do you think it would be possible to achieve solution like that? So I have data normalized and can easli create Pie Chart in Tableau.

 

#FeatureStepsPassedStepsFailedStepsSkippedStepsPendingStepsUndefinedStepsTotalScenariosPassedScenariosFailedScenariosTotalFeaturesDurationFeaturesStatusAT Job Name
Test cases for Matching8000081012.843PassedQS_Test_Automation_ITVT
Test cases for Standardization6000061013.277PassedQS_Test_Automation_ITVT
 

 

It pivots by Steps Values:

 

Pivot Field NamePivot Field ValueStepsTotal#FeatureFeaturesDurationFeaturesStatusAT Job Name
StepsPassed88Test cases for Matching2.843PassedQS_Test_Automation_ITVT
StepsPassed66Test cases for Standardization3.277PassedQS_Test_Automation_ITVT
StepsFailed08Test cases for Matching2.843PassedQS_Test_Automation_ITVT
StepsFailed06Test cases for Standardization3.277PassedQS_Test_Automation_ITVT
StepsSkipped08Test cases for Matching2.843PassedQS_Test_Automation_ITVT
StepsSkipped06Test cases for Standardization3.277PassedQS_Test_Automation_ITVT
StepsPending08Test cases for Matching2.843PassedQS_Test_Automation_ITVT
StepsPending06Test cases for Standardization3.277PassedQS_Test_Automation_ITVT
StepsUndefined08Test cases for Matching2.843PassedQS_Test_Automation_ITVT
StepsUndefined06Test cases for Standardization3.277PassedQS_Test_Automation_ITVT

 

 

 

JohnJPS
15 - Aurora

HI @dlesny,

For that I would first add a Record ID, then anchor on that to Cross Tab #Feature into the columns, then Transpose your "Steps" fields down to rows.

Does that work for you?

 - John

Labels