Challenge #128: Settle the Score

[KOBoyle]

Solution attached. When seeing the Total column in the output, I did happen to scroll down on the Crosstab tool and see that option. It is odd that when you check Sum only, the column headers are taken verbatim from the selected field. However, if you select Sum and Total Column it sticks the 'Sum_' prefix on column headers. I think it would be preferable to always take the column headers verbatim or have a prefix option so you don't have to undo the alterations made to the names.

[simons]

Spoiler

[fharper]

fun exercise

[alex]

Similar to others but I used the built in total functionality in the cross tab tool to get my totals.

Spoiler

[rupeshkanu]

Hi, 

Attached is my solution.

Regards,

RK

[pjdit]

another great challenge..  

Thanks

Paul

[mceleavey]

Ok, so my approach to this was to point at the mess they'd created and say loudly: "No! No! What have you done?" then made them go and sit in a corner and think about what they've done.

I then resolved the data problem as follows:

Spoiler

Nice and simple, I used Regex to tokenise to columns using any character, and created a formula using regex match to determine the case. This awarded 1 or -1 accordingly. 
I then used the data cleansing tool to change the scores to all lower case and cross-tabbed the data using sum, grouped by score with the round number as the header.
Separately, I took the sum of points by each score and joined it back to the pivoted data to give the total column.
I then simply applied a dynamic rename to add Round_ to the titles.

Simple.

I then refused to give them the results until they promised to learn how data should be captured and why.

[Aidan_K]

Hi,

This is my first challenge, really enjoyed it.....seems I've taken a bit of a longer road than some, but I got there in the end :)

[jzondervan]

Did it again! :)