Cloud Quest #7: Marvel-ous Comics

[AYXAcademy]

Hi Maveryx,

We posted the solution JSON file to Cloud Quest #6. 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. Download and extract the provided ZIP file containing your starting data and workflow files.
2. Upload the provided Cloud Quest 7 - Start File.json into your Analytics Cloud library.
3. Reconnect the provided charactersToComics.csv and comics.csv datasets to your starting workflow file.
4. Reconnect the Solution (Task 1).csv file to the Output Solution for Task 1 if you would like to review it. The Task 2 solution output is provided in the JSON 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:

Marvel Comics has a long history dating back to the 1930s and has created hundreds of characters we still find in superhero stories today. For today’s quest you will be blending and parsing data to identify every appearance of each character in Marvel Comics’ publication history (up to 2018).

Task 1: Use the provided datasets to determine the title, year, and issue number of every comic in which the characters in the Characters Text Input tool appear.

Task 2: Identify the first appearance of each character - year and issue number.

* Remove records without a publication year before sampling.

For those of you who are curious, titles without a published year are generally trade paperbacks and omnibuses that are collections of previously published comic issues. These are organized in narrative order rather than publication date and may include multiple titles contributing to the same storyline.

Hint: Use the RegEx tool to parse comic titles and publication years. Use a Summarize tool to group parsed data by Character Name, Comic Title, Publication Year, and Issue Number.*

*If you notice records with an issue number of -1, this is a numbering convention Marvel sometimes uses to indicate a prequel story. This will not affect your result.

Spoiler

A combination of the RegEx, Join, Summarize, Formula, Filter, and Sample 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
• Creating Regular Expressions

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!

[OllieClarke]

Here's my solution:

Spoiler

[phottovy]

Attached in my solution. Also, I noticed something unexpected when trying to debug my original logic. I initially made a mistake in my logic and tried to compare my result to the provided solution. When I attempted to join on the Character and Published year columns, the null values in the Published year column did not join together. I posted a more detailed write-up here: Unable to join on null values 

Spoiler

[mshishido]

a little bit different, but could get almost the same result

[ggruccio]

Slight detour while I fixed something that worked in Regex for the majority of records - 😀

Spoiler

[thomasduong]

Here is my solution for CQ7. Btw, I was having trouble loading the comics.csv file in the input tool. I changed the encoding which helped fixing the issue.

[patrick_digan]

Spoiler

[SGolnik]

another one done

[smurfitt]

Please find attached my solution, some slight differences in the order of the records as I prefer them sorted on issue number ;-) 

I did have some issues with the data for the Comics csv loading correctly,  it would load, but then it would not render the results in the tool anchors,  Not sure if that was expected, but did eventually get it to work by uploading the file into the datasets rather than via the workflow dialog.

[Carolyn]

---

@smurfitt wrote:

Please find attached my solution, some slight differences in the order of the records as I prefer them sorted on issue number ;-) 

I did have some issues with the data for the Comics csv loading correctly,  it would load, but then it would not render the results in the tool anchors,  Not sure if that was expected, but did eventually get it to work by uploading the file into the datasets rather than via the workflow dialog.

---

 Argh, that's where I'm stuck right now. I tried both uploading the file into the dataset and the workflow dialog but no luck on either :-/ 

[Carolyn]

Solved!

I had a lot of trouble getting the comics.csv file to work. It would upload but then wouldn't pull into the workflow. I ended up deleting all the "#" and the Description column and one of those fixed the issue. FYI @AYXAcademy 

Spoiler