Cross tab with more than one value field

[MRoyW]

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.

[atcodedog05 · Accepted solution]

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 solution]

Hi @MRoyW ,

I've attached a workflow for you.

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

M.

[CharlieS · Accepted solution]

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!

[atcodedog05]

Happy to help 🙂 @MRoyW 

Cheers and Happy Analyzing 😀

[MRoyW]

Thanks guys