I am fairly new to Alteryx, and I am trying to take a data set that looks like the below: AcountAcct DescLegal EntityLE DescAmount60000000SalesABC001ABC Company5000060001000Sales - OtherABC001ABC Company3010060000000SalesABC005ABC Limited7500060001000Sales - OtherABC005ABC Limited25250 And change it to look like the following: ABC001ABC005AcountAcct DescABC CompanyABC Limited60000000Sales500007500060001000Sales - 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.

Cross Tab with Multiple Column Headers

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

Acount	Acct Desc	Legal Entity	LE Desc	Amount
60000000	Sales	ABC001	ABC Company	50000
60001000	Sales - Other	ABC001	ABC Company	30100
60000000	Sales	ABC005	ABC Limited	75000
60001000	Sales - Other	ABC005	ABC Limited	25250

And change it to look like the following:

		ABC001	ABC005
Acount	Acct Desc	ABC Company	ABC Limited
60000000	Sales	50000	75000
60001000	Sales - Other	30100	25250

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.

Transformation

Accepted answers

Jim7

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!

example.yxmd

All comments

Jim7

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

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

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

Jim7

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

example.yxmd

twheeler

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

@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

example.yxmd

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167