Cloud Quest #8: Settle the Score

[AYXAcademy]

Hi Maveryx,

We posted the solution JSON file to Cloud Quest #7. Check it out and let us know what you think! Send suggestions to academy@alteryx.com or leave a comment below!

Let’s dive into this week's quest!

1. Upload the provided Cloud Quest 8 - Start File.json file into your Analytics Cloud library.
2. The input and output datasets are included in the start file.

For more detailed instructions on how to import and export Designer Cloud workflow files, check out the pinned article Cloud Quest Submission Process Update.

Scenario:

A group of friends are playing their favorite game. Not understanding the importance of inputting data in an easy-to-work-with format, they devised an incredibly inefficient way to keep score of their game!

In the game, there are five rounds of play and five players (a, b, c, d, and e—the first letter of their names). Each time the player’s initial appears in lowercase, the player is awarded 1 point. Each time their initial appears in uppercase, 1 point is subtracted from their overall score. Now it is your job to figure out each player’s score for each round and their respective totals after all five rounds of play. Who won? 

Hints: 

1. Tokenize the input data into rows.
2. There are many ways to calculate scores for each letter, but an If/Then statement can accomplish this in one expression.
3. When pivoting, you can perform multiple calculations at once including Sum and Total Column.

Spoiler

A combination of the Cross Tab, RegEx, Formula, and Dynamic Replace tools should solve your problem, but not necessarily in this sequence.

If you find yourself struggling with any of the tasks, feel free to explore these interactive lessons in the Maveryx Academy for guidance:

• Getting Started with Designer Cloud
• Building Connections in Designer Cloud
• Building Your Workflow in Designer Cloud

Once you have completed your quest, go back to your Analytics Cloud library.

• Download your workflow solution file.
• Include your JSON file and a screenshot of your workflow as attachments to your comment.

Here’s to a successful quest!

 

[bradshaw]

That took a bit longer than it should have.

Spoiler

[phottovy]

Another quest complete!

Spoiler

[mshishido]

here

[ggruccio]

Fun with Regex!  Others might have found a more efficient way to do this!

Spoiler

[logdog0411]

Spoiler

 

[thomasduong]

Here is my solution to CQ8

[patrick_digan]

Spoiler

[SGolnik]

Spoiler

 Fun one!

[smurfitt]

Initially I thought a manual table as there were only 5 players would make life easier to group and add the scores, but I also added a 2nd option which did not need the manual table entries.   Attached in my solution with the 2 different approaches.

[Carolyn]

Solved!

What was very interesting in a "I don't like this and this isn't how Desktop works" way - 

Spoiler

I set my If/Then to identify if a field was upper or lower case in formula expression #1. Then in formula expression #2, I turned all the Scores to lowercase so they would Summarize properly in my next tool. What I found was that formula expression #1 re-evaluated based on the results of #2, even though #2 came after #1



I got around it by just creating a brand new column called Player, but I wasn't expecting that to happen, since that's not how Desktop behaves



FYI @AYXAcademy - Not sure if this is a bug or how Designer Cloud is expected to behave. Let me know if I should make a Support ticket

Solution: 

Spoiler