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

Cross Tab with Multiple Column Headers

twheeler
7 - Meteor

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.

11 REPLIES 11
Jim7
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.

twheeler
7 - Meteor

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!

Jim7
8 - Asteroid

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

Jim7
8 - Asteroid

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

twheeler
7 - Meteor

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!!

twheeler
7 - Meteor

@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?

Jim7
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!

twheeler
7 - Meteor

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

Jim7
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