Hello,
I have always struggle to understand how to use the Cross Tab tool to get the results that I desire. Would somebody be willing to show me how get to the top format using the data from the the bottom format? It's different data, but the same formats. I appreciate any help that you can give to help me understand.
Thanks!
Mary
Alteryx really does not play nicely with merged cells or duplicate header names. You can make this format in your output by writing to an excel file in batches but really it is better practice to just use unique headers and not merge cells.
@mtatum
Parsing non-structured data is alway challenging and case-by-case.
Can you share some sample data?
Hi @mtatum ,
Alteryx is good at dealing with normalized data.
So it is not easy to output Excel-like pivot table which contains merged cells.
But if the table header can be "flattened" to one row, here is one solution.
Input Data
I changed the "Month" from char ("Mar", "Apr") to numbers (3, 4) as the Cross Tab sorts the columns in alphabetic order.
CPT | Charge Description | Visits | Charge Count | Year | Month |
1 | aaa | 5 | 6 | 2023 | 3 |
2 | bbb | 4 | 7 | 2023 | 3 |
3 | ccc | 3 | 8 | 2023 | 3 |
4 | ddd | 2 | 9 | 2023 | 4 |
5 | eee | 1 | 10 | 2023 | 4 |
Workflow
Formula
Name2 = ToString([Year]) + " " + PadLeft(ToString([Month]), 2, "0") + " " + [Name]
Output Data
If you stick to the Month in char, you may want to try Dynamic Rename Tool after Cross Tab.
CPT | Charge Description | 2023_03_Charge_Count | 2023_03_Visits | 2023_04_Charge_Count | 2023_04_Visits |
1 | aaa | 6 | 5 | ||
2 | bbb | 7 | 4 | ||
3 | ccc | 8 | 3 | ||
4 | ddd | 9 | 2 | ||
5 | eee | 10 | 1 |
I hope this helps. Good luck.
[deleted]
There are a few rules of thumb when it comes to using the Cross Tab Tool:
In your case, Rule 1 prohibits the immediate use of a single Cross Tab tool to achieve the desired results (*There are work arounds for this).
Considering Rule 2, I have assumed you would just use sum (if you can guarantee that only 1 value will ever appear for a given CPT and month/year combo, you still need to select an aggregation method, but it doesn't matter which you choose, although it is best practice to account for the case where you will need to aggregate).
Based on your input/output, grouping should occur on CPT & Charge Description (Rule 3)
Rule 4 is just an unfortunate and unavoidable side effect of the Cross Tab Tool.
Rule 5 requires that you either set up a sorting prefix to ensure proper formatting, or you hard code via Select Tool a column rearrangement.
To get your input and output I will showcase 2 options the simple way and the smart way.
Simple: Use 2 Cross Tabs, one for each segment of the data, and join the data back together on CPT code
Smart: Utilize Transpose Tool first to collapse Both Value columns into a single column, then Cross Tab
Hope this helps & Happy Solving!