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 |
Solved! Go to Solution.
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
Hi EstherB47. sorry for the delay. the image below may help explain the counting.
tough one, eh?
:)
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.
Cheers,