Hello Community Members,
A solution to last week’s challenge can be found here.
This challenge was submitted by Mark Thompson @Watermark . Thank you, Mark, for this Oscar-worthy challenge!
Every year as the Oscars roll around, John reviews and organizes his movie collection. He has a multi-tab spreadsheet with information about movies, including:
Name
Year of release
Format (DVD, BR, Disc, etc.)
Movie rating
Director
Ownership status (OWN indicates John has a copy, 1 means he intends to purchase)
The spreadsheet is divided into several tabs: Oscar best picture (BP), best director (BD), best actress, and best actor, among others. There are also "Top Lists" from various sources, including, but not limited to: IMDB, Variety, Time Magazine, and the British Academy of Film and Television Arts (BAFTA).
There is also another spreadsheet that has a list of the sheet names.
As you can imagine, the data is messy. It became too much work for John to maintain the spreadsheet, and he decided to leverage Alteryx to help him organize the data. John also wants to remove the On Line FilmTV Assoc and Warren Miller tabs from his spreadsheet, as he no longer wishes to maintain them.
Your task for this challenge is to create a macro that consolidates all Excel tabs into one spreadsheet. Include these categories:
Movie title
Year the movie was released
Movie rating
Status (whether John owns the movie or not)
Original spreadsheet tab it comes from
Need a refresher? Review the following lessons in Academy to gear up:
Introduction to Macros in Designer
Types of Macros in Designer
Creating a Batch Macro
Good luck!
The Academy Team
Download Start File
Source: Challenge creator's own private collection spreadsheet he put together over a number of years.
... View more
Hello Community Members,
A solution to last week’s challenge can be found here.
This challenge was submitted by our valued Community contributor, Jifeng Qiu. @Qiu, we appreciate your continued creativity in submitting engaging challenges.
May the Force be with us once more!
You have access to a survey about Star Wars, with responses collected from fans before the release of the seventh episode in the franchise, Star Wars: The Force Awakens.
Before diving into the challenge questions, you need to complete a few steps to clean and prepare the data for analysis.
HINT: For each task, you will be required to work on a unique question, outlined as follows:
Which of the following Star Wars films have you seen? Please select all that apply.
Please rank the Star Wars films in order of preference with 1 being your favorite film in the franchise and 6 being your least favorite film.
Please state whether you view the following characters favorably, unfavorably, or are unfamiliar with him/her.
However, the data is not straightforward. Each question may have multiple answers, and the fields related to the same question follow a pattern: the question itself, followed by unnamed fields that appear immediately after.
Once the data is prepared, you are ready to complete the following tasks:
Determine which Star Wars episode among the six films was the most viewed by survey respondents.
Create a graph to evaluate the correlation between how often films were viewed (from Task 1) and the rankings provided by respondents.
Identify the top two favorite characters (rated "Very Favorably") and the top two most disliked characters (rated "Very Unfavorably") based on unique respondents in the survey data.
Need a refresher? Review the following lessons in Academy to gear up:
Changing Data Layouts
Summarizing Data
Happy solving!
Data source: https://www.kaggle.com/code/samaxtech/cleaning-analyzing-star-wars-survey-data/input
The Academy Team
... View more
A solution to last week's Challenge can be found here!
A big thanks to those of you that joined us last week at Inspire for the Weekly Challenge session! It was so much fun solving with you all!
This week, we're identifying the most popular baby names that were registered between the years of 1880 and 2017. Given the provided dataset, determine the most popular names for Males and Females for each available year. The column "Field_1" contains three concatenated values: the name, the associated gender (Male or Female) and the number of occurrences that the name appeared in birth records. The column "FileName" contains the name of the file in which the record is found; the data was read in from a zip file that contained text files for each year (1880-2017) of records.
... View more
Hello Community Members!
A solution to last week’s challenge can be found here.
This challenge was submitted by our valued ACE, Yughandar Muley. Thank you, @ydmuley for this excellent contribution!
The holiday season is just around the corner, and it is the most magical time for businesses to thrive—and for seasonal employees to earn some extra money!
Meet Tom, a student who is looking to make some holiday cash. His family runs a homemade cookie business, and they need someone to pack orders during the bustling season.
Tom works 6 days a week and is paid $15 per hour, working 4 hours a day on weekdays, and 10 hours a day on weekends. He has Thursdays and holidays off, including Christmas on December 25th and New Year’s Day on January 1st.
Your challenge: Calculate Tom’s earnings for weekdays, weekends, and his total pay for the contract period! His start and end dates are provided in a Text Input tool in the workflow.
Let’s spread some holiday joy and solve this festive challenge!
Need a refresher? Review the following lessons in Academy to gear up:
Diving Into Expressions
Changing Data Layouts
Have a wonderful holiday season!
The Academy Team
... View more
A solution to last week’s challenge can be found here.
John has decided that it's time to adopt a dog. However, he has never owned a pet before. He is having trouble deciding on which furry friend would be best.
Your challenge is to help John make a choice.
You will use three datasets that contain all the information needed for the research. The ranking for each criterion goes from 1 (low/not good) to 5 (high/excellent).
John wants a dog that would suit his lifestyle best, and here are the most important criteria. A small dog that will adapt easily to living in an apartment and doesn't bark too much, as neighbors would complain if barking is excessive. Since John is a novice owner, the dog needs to be easy to train and friendly with strangers.
Use these five criteria to select three dogs. Then create a report showing your findings and help John make his decision.
Following our users' comments, this Weekly Challenge has been updated. Thank you for your participation.
... View more
