Alteryx Academy

Hi Community members,   A solution to last week’s challenge can be found here.   This challenge was submitted by John Primeaux (@jeprime) . Thank you, John, for your submission!   This week, you’re stepping into the role of a data quality detective. A data table has been populated with critical tracking codes—but many of them have been entered incorrectly.   Each code is supposed to follow a very specific structure. This format is vital because these codes are used to locate corresponding documentation within a larger database. Your mission: clean them up and restore order.   The required format is 1 to 4 alpha characters, a hyphen, 3 digits, a hyphen, 2 digits, then one optional alpha character.   The numeric portions (3-digit and 2-digit groups) are always generated correctly by the system. However, delimiters (- vs. _) may be missing or incorrect.   Hint: Assume that the three digits and two digits inside the code are created by a computer; therefore, the hyphen is never missing.   Task 1: Create the code in the correct format (using only hyphens "-"), including the optional letter suffix, and compare it to the old code.   Task 2: Calculate the percentage of codes with a letter suffix, as well as the percentage of codes that are missing or contain incorrect delimiters.   Once you have completed your challenge, include your solution file and a screenshot of your workflow as attachments to your comment.   Good luck! The Academy Team   Download Start File   Source: Dataset generated by challenge creator. ... View more

A solution to last week’s challenge can be found here.   We learned our lesson last week at the International Bank of Awesome Data (IBAD) – data is less than awesome if it isn’t standardized & clean. So now that we have our data standardized, we have to tackle the remaining outlier – the Amount fields.   Being that IBAD is an international bank of such great renown, they happily accept currency of all forms – dollar, peso, euro, franc, pound, yen, kuna, krona, Monopoly money, etc. However, different countries/regions/banks have different norms when it comes to applying numeric formats to the amounts they report back to headquarters. Some use symbols or currency codes to indicate the currency type, some put negative signs on the right instead of the left, some use commas instead of periods for the decimal indicator, etc. Additionally, these banks report customer balances in the customer's local currency (rather than the bank's local currency) so a conversion will be necessary to get all our amounts into the same currency (USD, for reporting purposes).   OUR CHALLENGE: Since we have to do this same exercise A LOT (cleaning up number formats & converting to USD), we have been asked to create a standard macro that will perform these steps. Since they come in all shapes & sizes, our Number Formatter macro needs to be flexible enough to deal with a variety of different number format scenarios.   THINGS THE MACRO SHOULD DO FOR ANY AMOUNT FIELD SELECTED: Step 1: Remove dollar signs and/or any other non-numeric characters (letters, symbols other than decimals/commas, spaces) Step 2: Based on the Debit/Credit field, apply a negative sign to the Amount field if there isn't one there already (AND make sure it is on the left side of the number value) Step 3: Look up the current conversion rate to USD using the CurrencyLookupList, and include both the “USD Exchange Rate” and “Decimal Indicator” fields Step 4: If Decimal Indicator field is a comma, swap the commas for periods so that the decimal indicator is a period for all records Step 5: Final "Exchanged Amount" should be numeric data type Fixed Decimal with 6 decimal places, converted using the Exchange Rate provided for that Currency Code.   The end goal should be a standard macro that can be used to clean up the Amount field on all 3 of the regional files (whether separate or combined). One indication that you're in good shape will be if you can run the workflow/macro without receiving any conversion errors or warnings!   * For an extra challenge, build your macro in a way that will allow you to run it for the Opening Balance field as well, with an option to NOT apply the Debit/Credit logic! ... View more

A solution to last week’s challenge can be found here.  Source: https://www.dailymail.co.uk/news/article-4965690/Humpback-whale-breaches-Sydney-Harbour-sunset.html   Every year whales around the world migrate from their feeding grounds to their breeding grounds. Some species travel an outstanding 12,000 miles round trip!   Use the data sets below to analyze migration movements for different whale species. The file Whale_Migration_Data contains a list of whales whose migration patterns have been tracked from 2001-2003, and the total kilometers each whale swam on each day of their migration. The file Whale_Pods contains a list of whale ids and pod ids, which indicates the pod each whale belongs to. The file Whale_Species contains information about the whale species of each pod.   Find the total number of kilometers each pod swam for migration for each year, then find the average number of kilometers each whale in that pod swam (total kilometers / number of whales in the pod). Then, find the pod with the highest average kilometers swam per whale for each species for each year. ... View more

A solution to last week's challenge can be found here.    The Wareki is a Japanese date format which includes an extra character to signify the era. The eras indicate the emperor at the time and they impact the year only. For example, H04/01/31 represents the 4th year of Heisei era, but the 01/31 is in fact January 31st of that year.   Modern Japan, which is considered the period beginning in 1868, is currently in its 5th era. This week's challenge is to create a macro that converts Wareki dates into Alteryx Datetime or vice versa. The macro should include a configuration option for Wareki to Date or Date to Wareki. ... View more

The Control Container Tool introduces dynamic capabilities to Alteryx workflows, enabling sequencing, metadata utilization, and conditional execution for smarter, more flexible processes. ... View more