Alteryx Academy

  Hello Community members,   A solution to last week’s challenge can be found here.   This week’s challenge was submitted by Patrick Digan (@patrick_digan) . Thank you, Patrick, for submitting this great challenge!   In this challenge, you will explore an important part of the financial system: bank failures. Specifically, you will be working with data provided by the Federal Deposit InsuranceCorporation (FDIC) on the 569 banks that have failed from 2000 to August 2024. The FDIC was established in the 1930s to step in and help with bank failures.   The two datasets provided contain the following information: banklist.csv: A list of failed banks that includes information on the city and state where the bank was established, the certification number, whoacquired the failed bank, the closing date, and funds. institutions (select data).csv: A dataset with details on each bank’s establishment date (INSDATE) and certification number (CERT). Your task is to calculate the number of days from a bank’s establishment to the day it closed. Then, determine which bank failed in the shortest number of days between its establishment and closing.   Ready to uncover which bank had the shortest lifespan? Let’s get started.   Need a refresher? Review the following lessons in Academy to gear up: Joining Data Diving into Expressions Good luck!   The Academy Team   Download Start File   Download Solution File   Sources: https://www.fdic.gov/resources/resolutions/bank-failures/failed-bank-list/index.html https://banks.data.fdic.gov/docs/#/History/getHistory ... View more

Hi Maveryx,   We posted the solution JSON file to Cloud Quest #14. 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!   Download the provided JSON file containing your starting data and workflow files. Upload the provided Cloud Quest 15 Start.json file into your Analytics Cloud library. All necessary datasets are contained within Text Input tools in the workflow.   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:   Fantasy Football season is almost upon us in the US so there is no better time than now to evaluate how well kickers perform. This year, Yahoo! Fantasy Football has introduced a new wrinkle into how place kicker scoring is calculated.   In previous years, field goal points were earned based on the distance of the field goal, but only in ranges: 0–39 yards = 3 points, 40–49 yards = 4 points, and 50 or more yards = 5 points.   In 2024, a new method has been introduced that gives 0.1 points per yard for each successful field goal attempt, meaning: a 25-yard field goal will be worth 2.5 points, a 33-yard field goal will be worth 3.3 points, a 56-yard field goal will worth 5.6 points, and so on.   A list of kickers who are on current NFL rosters has been provided in the NFL Kickers Text Input tool. The 2023 Field Goals Text Input tool contains the distances of each field goal they kicked per week last season. The objective of this Cloud Quest is to:   Determine the difference in points scored for each kicker from the old calculation method to the new calculation method. Then calculate the average change in points scored by all kickers. Round the average point difference to tenths of a point. List the top five kickers, their pro teams, their points using the old method, their points using the new method, and the difference in points the new rules would have on their total fantasy points. Solve the bonus question. The points after touchdown (PAT) from 2023 have also been included in a third Text Input tool. As a bonus question, use this data to calculate the total points earned for each kicker and list the top five kickers calculated with the old rules and new rules.    Hint: To answer Tasks 1 and 2, remove Null records. To answer the Task 3 bonus question, replace Nulls with 0.   A combination of the Summarize, Join, Transpose, Sample, and Formula 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 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!       ... View more

A solution to last week’s challenge can be found here.   To solve this week’s challenge, use Designer Desktop. This challenge comes to us from @atcodedog05 . Thank you for your contribution.   Are you a fan of the sitcom The Office and the character Michael?   The dataset contains information about the seasons, episodes, scenes, and lines of the series. For the first part of this challenge, we will use some of the dialogue and data to: - Determine in how many seasons the character Michael appeared. - Determine in how many episodes the character Michael appeared. - Find out, based on the provided dataset, how many lines the character Michael had. - Find out, based on the provided dataset, how many words the character Michael had.   Data Source: www.kaggle.com/datasets/nasirkhalid24/the-office-us-complete-dialoguetranscript   ... View more

Hi Maveryx,   We posted the solution JSON file to Cloud Quest 13. 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!   Download and extract the provided JSON file containing your starting data and workflow files. Upload the provided Cloud Quest 14 Start.json file into your Analytics Cloud library. All necessary datasets are contained within Text Input tools in the workflow.   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:   For today's Cloud Quest, imagine you are an analyst for a manufacturing company that operates 24 hours a day. Because they operate 24/7, there are two production crews working each day. The day shift starts at 6:00 a.m. and ends at 6:00 p.m., while the night shift starts at 6:00 p.m. and ends at 6:00 a.m. the following day. To ensure that the day and night shift crews are achieving similar results, all daily reports have to show production results for each shift.   The dataset you have been given contains a list of dates and times of when production data was collected. Your task is to reformat the dates and times to reflect the appropriate shift the production data belongs to. One full day of production starts at 6:00 a.m. and ends at 5:59 a.m. the next day. This means that all production during midnight to 6:00 a.m. gets recorded as daily production for the previous calendar day, because that is when the crew started their shift. For example, a record showing 5/7/23 09:00 a.m. would be recorded as the 5/7/23 Day Shift, but 5/7/23 03:00 a.m. would be recorded as the 5/6/23 Night Shift.   After you have changed the dates to their appropriate shift date, answer the following questions: Which day shift and night shift had the most records? What is the average number of records collected for each shift?      Hint:  RegEx expressions can be used in the Formula tool as well as the RegEx tool. You can "group by" and sort in the Designer Cloud Sample tool. A combination of the Summarize, Formula, DateTime, Select, and Text to Columns 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!   ... View more

Hi Maveryx,   We posted the solution JSON file to Cloud Quest #12. 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!   Download and extract the provided JSON file containing your starting data and workflow files. Upload the provided Cloud Quest 13 - Start File.json file into your Analytics Cloud library. All necessary datasets are contained within Text Input tools in the workflow.   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:   You are a big fan of Formula 1, and you want to run some simple statistics.   Your input data contains information about teams and their drivers from 2019 to 2021. Using this dataset, answer the following questions:   What is the average age of all drivers in the field? Calculate the number podiums each team accumulated in 2019, 2020, and 2021. Which team accumulated the most total podiums, and how many did they accumulate? Which team had the biggest point differential between their best and worst drivers in 2021? Which country produced the most drivers, and how many drivers are from that country? (According to their Place of Birth)   Hint: Did you know you can create new columns based on a summarize function within one Cross Tab tool?   A combination of the Summarize, Cross Tab, Sample, and Formula 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!     ... View more