Lessons Learned Climbing the Alteryx Weekly Challenge Tracker

In this blog I'd thought I'd share some lessons of my first 3 weeks of Alteryx (the first two of which were self-taught) that culminated in a position on the top 10 challenge solvers in the past 30 days and passing both the Core and Micro Credentials with high 90% marks.

Understanding the implications of the Group By function in Summarize, specifically the granularity of income versus outcome, is crucial. I learned that noting at which level the data needs to be aggregated to achieve a successful outcome is an important lesson. With practice, this process became easier, which was a noticeable positive of the experience. Additionally, I gained a better sense of when aggregation must occur as part of a cross-tab (row to column pivot). Also, I gained familiarity with the need to branch out into two paths before rejoining the data. An example that comes to mind is calculating % of total, where it makes sense to use two separate summarize tools - one grouped by and one not - for the total before appending the totals to the group sums and using a formula to calculate % of total.

More a quality of life suggestion for newer Alteryx users is the comparatively hidden Dynamic Rename tool that I noticed had two fairly useful purposes thus far, bringing the first row of data up to column headers and also bringing column headers across from the meta-data of a different data source. This can be useful to quickly rename column headers to match the expected outcome of a challenge.

Getting the correct column headers is perhaps an unnecessary detail for completing the challenge but it came from the use of Crew Macro’s Expected Equal tool which was a blessing and a curse. In some ways it was a blessing allowing me to quickly check my results to the expected outcome with either the error messages built in to the test or by quickly switching between the two input anchors of the tool that show expected outcome and actual outcome. However, I consider the tool a curse in some ways as I got drawn into the minutia of the challenges rather than the skills they were meant to improve. If the original solution had what appeared to be an error I found myself bizarrely trying to replicate an error in pursuit of true replication. When sorting was mixed due to use of the AMP engine I grew frustrated at the additional steps I would add to try and get that perfect match. Going forward, I would like to use the check a bit less or at the very least be more disciplined when I cannot seem to generate an exact match.

One of the most essential operations in string manipulation is the use of string functions, which are Alteryx formulas that allow you to perform various operations on strings, such as finding the length of a string, converting a string to lowercase, or uppercase. Within string manipulation, position indexes starting at 0 is an important concept to understand. A substring is a smaller string that is contained within a larger string. And, the position index starting at 0 refers to the position of a character within a string, while the second specifier identifies how long the substring is - in Tableau terms this would be equivalent to the mid function. Substring was an important part of learning because it was slightly more flexible than left or right at isolating key information in a string without knowledge of Regex yet.

Multi-row were also something that came up fairly often in my small sample of experience thus far. The interface for the tool is comparatively clunky to the formula interface for example but getting a sense of how it worked was useful for challenges that had counting instances or a need to fill in the gaps of rows by copying from the row above where necessary.

Calling on the previous row where necessary in a conditional clause became more familiar with practice. The example below from Challenge 196 can be broken down fairly easily:

We are making a new field called quarter but they have relative start dates to the client ID. So we group by ClientID instructing Alteryx to group on these values and reset the formula when necessary. The if statement says if the contract date is the same as their first contract date (computed earlier with summarize then joined) then return 1 (for cohort 1) otherwise consult the row above and add one to the quarter value produced in that row. This formula works on the basis that the data was sorted and ordered without missing quarters.

Throughout the challenges I had some issues with the datetime parse tool - in Challenge 167 it was parsing the dates correctly but perhaps because it was forced to include time as well - the datediff function that I used after was not computing the correct values. Over time I have begun to use datetimeparse() as a function within the formula tool. It lead to correct values in challenge 167, it made me more familiar with the date time specifiers like %y for a 4 character year and appeared to give me more flexibility in the data-type and form of my output.