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.
Solved! Go to Solution.
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.
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!
No problem, let me know if you run into any issues with setting up the report and I'm happy to help!
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!!
@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?
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!
Thanks so much, Jim! Lots to learn still in Alteryx and think about. Really appreciate the examples and the help!
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.