How to transpose/Group data columns and row into reporting table

[SSali]

Hi all,

Apologies for the ambiguous title, however I was not sure how to best describe the issue I am trying to resolve.

Currently I have a table which has a number of ID's with attributes associated and they are replicated for multiple timestamps.

I have created a formula tool to perform some data validation on each attribute (i.e. attribute needs to be between 0 and 1 etc.) and the results are then in the validation rule columns.

I now have the structure of output that is attached to this post:

COB_DATE	VR_COB_DATE	VR_ID	VR_O_Rating	VR_OD	VR_G_Rating	VR_Segment	VR_Entity	VR_CLA	VR_GLA
3/31/2018	Pass	Pass	Fail	Fail	Pass	Pass	Pass	Fail	Pass
12/31/2017	Fail	Pass	Fail	Pass	Pass	Fail	Pass	Pass	Pass
12/31/2017	Pass	Pass	Fail	Pass	Fail	Pass	Pass	Pass	Fail
3/31/2018	Pass	Pass	Fail	Pass	Pass	Pass	Pass	Fail	Pass

What I am trying to get to is the following structure:

COB_DATE	Rule	Fail	Pass
12/31/2017	VR_COB_DATE	18	359
3/31/2018	VR_COB_DATE	13	297
12/31/2017	VR_ID	5	372
3/31/2018	VR_ID	5	305
12/31/2017	VR_O_Rating	0	377
3/31/2018	VR_O_Rating	0	310
12/31/2017	VR_OD	0	377
3/31/2018	VR_OD	0	310
12/31/2017	VR_G_Rating	124	253
3/31/2018	VR_G_Rating	103	207

The way i did it initially led to an insane amount of summarize, arrange and cross tab steps (1 of each for every rule) and some of the files i am validating may have up to 20 rules which is messy.

There must be a way to have a single flow of steps to transpose and group the data like above without doing it rule by rule?

Thank you in advance for anyone that can help, I am still very new to Alteryx but see a lot of power in it so hoping to make it work.

I have attached and example of the input data for your reference.

[BenMoss · Accepted solution]

Transpose your data with your date field as the key field and everything else as a data field.

Then perform a summarize, grouping by your date, name and Value field, then adding a count of the value field too.

Finally cross-tab your data. The date and name field will be your group by fields. The header field will be your name column and the data field will be the count column.

I did this mobile with no view of the data you attached but based on the sample data in your post this should work.

Ben

[jarrod]

You actually almost answer your own question. :) First you'll transpose then you'll cross tab. See my example for a better explanation (3 tools to transform the data)

[SSali]

Ben, you are an absolute rockstar!!

I am still learning how to use all the little gadgets in Alteryx but this worked a treat :)

Thank you so much.