Move row data to column headings and count occurances

[EmAnna]

Hi all, 

Again, am new to Alteryx (amazing software!)

I have the data in red below, but want it to look like the green. 

Anyone know how to do? Thank you!

[gautiergodard]

Hey @EmAnna 

Welcome to Alteryx!

Here is one way you could do this:

Hope this helps!

[DataNath]

Hey @EmAnna here's how I'd go about this:

1) Transpose the data, bringing each of the values (1,2,3) into a single field

2) Summarize the data, grouping by Station/Location to maintain that split, as well as [Value] to group by the outlet and then get each of their counts:

3) Filter out null values in order to prevent an additional field in the end result for nulls:

4) Cross-Tab the data to bring your outlet names into the headers and their counts into the rows:

5) Apply a Multi-Field Formula to convert all null values into 0s to clean up results:

Hope this helps! Please shout up if you have any questions.

[EmAnna]

Hi - thanks so much for the above!

I understand Step 1 transpose, Step 5 non-null 

Could you explain step 2-4 a bit more? Understand how cross tab works, but the count bit is kinda new? 

Thanks!!

[DataNath]

No problem @EmAnna. Absolutely!

So in step 2 (Summarize) we're basically saying to Alteryx... If we split the data into each Station/Location/Store combination, how many times does that appear. For example, for station A, we further group by Peterborough and then BP, for which there is 1 occurrence and so that brings back the count of 1 - same for Greggs. For the other stores, these never appear within that Station & Location and therefore their count is null (zero).