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

Transpose or Crosstab, or other

Asteroid

Greetings. 

I have the following data table that I need to report as the table below it.  I need the column header to be specific to 3 Act#:SA111, SA555, and where the unit has both SA111 and SA555.  Any help would be greatly appreciated.  I attached an Alteryx that contains the data input file only.

unitAct1Act2Act3Act4Act5   Act6Act7
123SA111SE222     
234SA111      
345SA555      
456SA111SE222SA555    
567SA666      
678SA111      
789SA111SA555SE222SE666   
890SA111SA555SE222SA666SE777SE888SE898
901SA111      
012SA555      

 

 

ActSA111SA555SA111_SA555Totals
SA1114  4
SA555 3 3
SA111_SA555  33
SA666 112
SE2221 34
SE666  11
SE777  11
SE888  11
SE898  11
totals541120

 

Quasar

Hi @LincolnMike ,

 

Would you explain the summaries in the bottom table? When I'm counting, for example, how many units have the Act of SA111, I count 7, not 4.

 

Cheers!

Esther

Asteroid

Hi EstherB47.  sorry for the delay. the image below may help explain the counting.

CrossMeUp.JPG

Asteroid

tough one, eh?

:)

Alteryx Certified Partner
Alteryx Certified Partner

Hi @LincolnMike 

 

This was not a very easy one, but some Summarize Tools and Transpose can do the trick.

 

Summ.PNG

 

- Transpose all the Act fields and clean the empty values

- Summarize Tool to group by Unit and list all codes

- Filter SA111 and SA555 codes 

- True side you use Summarize Tool to Concat the SA111 and SA555 codes when they appear together in a Unit

- Join the True side with the False side by Unit - So you can mantch the Concat Codes that have the other codes of interest
- Parallel to this, the True side is also used to count the occurences of SA111, SA555, and SA111_SA555
- Summarize the Joined side to count the occurrences of the other codes compared to the Concat Codes
- Union Everything
- Cross-tab data to move the Concat Codes to the Columnn Headers

 

Detailed Workflow appended.

 

To add Row and Column Totals, I recommend you to read this article. Some extra steps are needed.

 

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Everything-You-Need-To-Know-About-Adding-Tot...

 

Cheers,

Asteroid
Thank you Thableaus. I applied that method to my 1,000,000 units and the outcome is what I expected.

Cheers!
Labels