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
SOLVED

Pivoting the data

Highlighted
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

ACE Emeritus
ACE Emeritus

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!

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. 

ACE Emeritus
ACE Emeritus

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.

 

ACE Emeritus
ACE Emeritus

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

Capture.JPG

 

ACE Emeritus
ACE Emeritus

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.

 

Meteor

This is fantastic, thank you John! 

ACE Emeritus
ACE Emeritus

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.

 

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

 

 

 

ACE Emeritus
ACE Emeritus

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