Help with Cross Tab

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

Final Output.png

Horizontal Data.png

Developer Tools

Accepted answers

All comments

griffinwelsh

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.

Qiu

@mtatum
Parsing non-structured data is alway challenging and case-by-case.
Can you share some sample data?

Yoshiro_Fujimori

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.

Sample_Pivot.yxmd

Andrew_Merrill_APRx

[deleted]

CoG

There are a few rules of thumb when it comes to using the Cross Tab Tool:

You can only "Pivot" 2 columns. The distinct rows of the first column will become the new column names, and the second column will distribute its values across those newly created columns (called buckets henceforth).
If multiple rows exist that fall into the same bucket, then they will be aggregated via the method of your choosing (making sure you are working with the right datatype)
Values that you do not want aggregated must be "grouped" by other columns that distinguish between those records
This tool replaces all special characters (including spaces) with underscores
It will sort the new columns alphabetically

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703