Our team heavily utilized Alteryx and its plethora of analytical tools to solve the series of challenges we were faced with. After opening the provided file (HackCU2018_AlteryzChallenger.yxdb) in Alteryx, we noticed that it contained a single string consisting of 1s and 0s. The first component of the challenge was to convert this string to ASCII. We did this by tokenizing it into chunks of 8 bits, running BinToInt on each chunk, and then CharFromInt on each chunk again. In addition to this, we noticed that colons were not recognized as characters, so when an unidentified character appeared, we replaced it with a colon.
When analyzing the data we saw that it output a series of URLs pointing to JSON files with a JSON selector and number appended to each URL. We tokenized this to split it up into the 247 different URLs and added two additional columns for the attribute and number. From there, we realized that the number referenced the character at that index in its associated selector. We were able to use the Filter tool to easily extract only the JSON values that matched the selector appended to the URL, and then the Formula tool to extract only that character from each value.
After concatenating the characters, we had a string consisting of digits, underscores, periods, and dashes. Due to some irregularities in the string, we suspected that it may have not been the intended result. The Alteryx booth confirmed our suspicions and provided us with an updated string. This string was far more regular, and we believed that it could somehow be translated into coordinates. The first two values were coordinates that pointed to Chamonix, Switzerland. We were under the impression that this referred to the Winter Olympics that were hosted in Chamonix in 1924. However, the next coordinates on the text string brought us to a Native American reservation in Utah, so we abandoned the idea that the the text string was comprised of Winter Olympic host locations.
Our team spent hours trying to figure out the significance of the string file, but could not find a pattern. After multiple conversations with the Alteryx booth, it was discovered that an API was updated and the string file was again incorrect. After receiving the correct text string, we quickly realized that our original Winter Olympics theory was correct. The correct string file contained a series of coordinates with an underscore separating the latitude and longitude, and the comma being a delimiter for different points. We also ran into an issue where some data points could not be read, but that was because the latitude and longitude were swapped. This resulted in a series of 24 points, which Alteryx conveniently let us view on a map. When analyzing the points, we found that the points each represented the location of one of the 23 Winter Olympics and the 2022 Winter Olympics that will be held in Beijing. We then carefully considered the challenge question: “What’s the attendance of a specific event?” Since we can easily get the number of athletes in attendance for the 23 previous Winter Olympics, we decided to utilize Alteryx to try to create a regression and predict the attendance of the 2022 Winter Olympics in Beijing.
First, we used the Charting tool to create a scatter plot of a dataset collected manually by Rahul from Wikipedia (of the number of athletes at each Winter Olympics). The data seemed to follow a linear path until a certain point, at which it changed to a different linear path. We decided to apply a linear regression to the latter portion of the data mentioned above.
Unfortunately, the version of Alteryx Predictive Tools we had available was not compatible with the version of Alteryx we were using. We were eventually able to download the correct version of the Predictive Tools and run the Linear Regression tool, which resulted in a predictive equation of y = 51.2x - 100347.9, where y is the number of athletes and x is the year. By plugging 2022 into our equation, we predict that approximately 3,179 athletes will be in attendance at the 2022 Beijing Winter Olympics.