Cross tab with more than one value field

Question

How can I make new column headers based on row information where the values are in two columns?

For example, the sample output required:

DateActual RevenueActual COGSBudget RevenueBudget COGS1/12/122114 1212001/01/10004535234219/04/17006456304516/01/192425133500

From the current file I have of the form:

DateTypeRevenueCOGS1/12/12Actual211412121/01/10Budget4535234219/04/17Budget6456304516/01/19Actual24251335

The actual data would have both actual and budget performance for each date.

atcodedog05 · Accepted Answer

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 😀👍

mceleavey · Accepted Answer

Hi @MRoyW ,

I've attached a workflow for you.

This was achieved with a bit of Transpose and Crosstab magic.

M.

CharlieS · Accepted Answer

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!