Transpose or Crosstab, or other

[LincolnMike]

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.

unit	Act1	Act2	Act3	Act4	Act5	Act6	Act7
123	SA111	SE222
234	SA111
345	SA555
456	SA111	SE222	SA555
567	SA666
678	SA111
789	SA111	SA555	SE222	SE666
890	SA111	SA555	SE222	SA666	SE777	SE888	SE898
901	SA111
012	SA555

Act	SA111	SA555	SA111_SA555	Totals
SA111	4			4
SA555		3		3
SA111_SA555			3	3
SA666		1	1	2
SE222	1		3	4
SE666			1	1
SE777			1	1
SE888			1	1
SE898			1	1
totals	5	4	11	20

[estherb47]

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

[LincolnMike]

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

[LincolnMike]

tough one, eh?

:)

[Thableaus · Accepted solution]

Hi @LincolnMike 

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

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

[LincolnMike]

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

Cheers!