Cross tab with more than one value field
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
How can I make new column headers based on row information where the values are in two columns?
For example, the sample output required:
Date | Actual Revenue | Actual COGS | Budget Revenue | Budget COGS |
1/12/12 | 2114 | 1212 | 0 | 0 |
1/01/10 | 0 | 0 | 4535 | 2342 |
19/04/17 | 0 | 0 | 6456 | 3045 |
16/01/19 | 2425 | 1335 | 0 | 0 |
From the current file I have of the form:
Date | Type | Revenue | COGS |
1/12/12 | Actual | 2114 | 1212 |
1/01/10 | Budget | 4535 | 2342 |
19/04/17 | Budget | 6456 | 3045 |
16/01/19 | Actual | 2425 | 1335 |
The actual data would have both actual and budget performance for each date.
Solved! Go to Solution.
- Labels:
- Preparation
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @MRoyW
Here is a workflow for the task. I have added both actual and budget for first 2 dates.
Input:
Output:
Workflow:
Hope this helps 🙂
If this post helps you please mark it as solution. And give a like if you dont mind 😀👍
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @MRoyW ,
I've attached a workflow for you.
This was achieved with a bit of Transpose and Crosstab magic.
M.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @MRoyW
The way I would go about this is to Transpose the data first so you can use a formula expression to combine the [Type] values with the header names. Then use a Cross Tab with the new field names and that's most of it. I also added a Data Cleansing tool to replace nulls with 0s. Since your date values are not in chronological order, you could use a RecordID tool at the beginning so you can keep that original order of records.
Check out the attached workflow to see an example of how this all can be done and let me know if you have any questions. Happy Alteryx-ing!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks guys
