Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
SOLVED

Cross Tab with Multiple Column Headers

Highlighted
Alteryx Partner

I am fairly new to Alteryx, and I am trying to take a data set that looks like the below:

 

AcountAcct DescLegal EntityLE DescAmount
60000000SalesABC001ABC Company50000
60001000Sales - OtherABC001ABC Company30100
60000000SalesABC005ABC Limited75000
60001000Sales - OtherABC005ABC Limited25250

 

And change it to look like the following:

 

  ABC001ABC005
AcountAcct DescABC CompanyABC Limited
60000000Sales5000075000
60001000Sales - Other3010025250

 

I can use the cross-tab tool to get either Legal Entity or LE Description as the column header, but how do I get two columns, as shown?  Any help would be greatly appreciated.

Highlighted
8 - Asteroid

How important is it for you to have the Legal Entity in a separate row above the main report? Alteryx doesn't allow you to have null or multiple headers for the same field.The simplest solution would be to write a formula to combine Legal Entity with LE Desc and place the combined name in the same header. 

 

If it's really necessary to have Legal Entity as the top row, you can use the reporting tools to create a reporting element with just the Legal Entity information, then join that with the separate table in your Layout.

Highlighted
Alteryx Partner

Jim -

 

Thanks so much for your response.  It is important for me to have the legal entity as the top row, as the output is going to feed another system that requires this specific format.  I will give your second option a try and see if I can make it work.  I really appreciate your help!

Highlighted
8 - Asteroid

No problem, let me know if you run into any issues with setting up the report and I'm happy to help!

Highlighted
8 - Asteroid

Here's an example of the table solution, which you can adjust as needed to reflect your particular data.

Highlighted
Alteryx Partner

Thanks so much, Jim!  I hadn't gotten back to this yet.  I will check it out and let you know if I have additional questions.  Very much appreciated!!

Highlighted
Alteryx Partner

@Jim7 One more question: in the example provided, you use the Select Records tool twice and then union them together.  This works, because there are only 2 entities in the sample data provided.  However, I have several hundred entities in my real data set and can't do a Select Records tool for each one.  Is there another way to pivot the legal entities from one column to rows?

Highlighted
8 - Asteroid

Sure, attached is an example which dynamically reads in whatever number of fields you have. It assumes the first 2 fields in the stream are blank like in your example, but if you anticipate needing more blank fields you can just add them to the Text Input using the same format (!3, !4, etc.). Hope that helps!

Highlighted
Alteryx Partner

Thanks so much, Jim!  Lots to learn still in Alteryx and think about.  Really appreciate the examples and the help!

Highlighted
8 - Asteroid

Happy to help! If you don't mind marking the thread as Solved that would be great. Let us know if you have any other questions/issues.

Labels