Last week, I wrote a short summary on the Alteryx Challenge idea. The objective is to finish all Alteryx Weekly Challenges by the end of the DS training. This is my first blog in the series describing the lessons learnt and the tools used to complete the challenges. This one is mostly about certain tools that I’ve found myself using a lot, no matter the topic. For specific subjects, scroll down to see the list of my posts.
Weekly Challenge Structure
As far as I know, every alteryx flow starts with an input (Input Data), this is provided by Weekly Challenge. After running the flow (ctrl+r) you can see how the data looks like. The start files (see below) also give you the expected output (Output Data) and the task is to create a flow that transforms the data into a required format.
This week I’ve been introduced to Crew Macros (download here, thanks Jonathan!). One of them allows comparing datasets in a form of a test. Once installed, find CReW Test and use Expect Equal (BETA) to check whether your output matches the expected one.
A word of caution
It’s possible that there’s a mistake in the solution (expected output). If you get errors, double check your solution to make sure the flow you created is doing what you wanted it to do and that your logic is correct. In case everything on your side is fine, you can check replies under the Weekly Challenge to see if others had the same reservations. Once you’re sure you’re right, I’d just comment when submitting the solution (you submit it by responding to the original post and including your solution file).
Now that we have that sorted, let’s get to some tools.
Tools
Formula Tool – fairly intuitive if you had some experience with programming, just need to get used to the syntax (fairly easy). If you click on blue icons on your left-hand side, you can see the list of functions, variables, etc. you can use, also you’ll get suggestions once you start typing. If you start with ‘[‘ the suggestions will start with the available fields.
Important things to remember when setting a formula tool:
- Do you want to overwrite a field or create a new one?
- If the latter, what data type do you want it to be? (I tend to keep forgetting about this one and realise something’s wrong when I get errors from other tools…)
- You can create multiple separate expressions within one formula tool. They seem to be run sequentially in the order they are in the tool configuration.
The Alteryx documentation seems sensible and I find myself using it a lot. I even added the functions section to bookmarks in my browser! If you plan to do a lot of challenges, I suggest you do the same. It’s just easier for me to figure out what kind of function I need and how to use it.
Multi-Field Formula and Multi-Row Formula. There are some things that you need to do for a few fields or it may be easier/necessary to use values from nearby rows to get what you want. For these cases, you’d likely use multi-field and multi-row formula tools respectively. Multi-Row Formula allows grouping by certain fields so it’s quite helpful if you need to create a sequence of numbers for each group (eg. month number for all months for multiple years, see below).
Generate month numbers with Multi-Row Formula Tool.
Whenever the Year value changes, the Month field starts from Null() as there was no Month field before. Once that first value is changed to one, the next values just increment on the previous ones.
If you want to do a combination of multi-row and multi-field you can either use a few of multi-row formula tools or transpose the fields of interest, add a multi-row formula and cross-tab it back to normal (thanks Ben for that tip!).
Generate Rows – as the name suggests, it generates rows. However, if you use a custom expression (‘…’ on the right-hand side in the configuration next to ‘Condition Expression’ text box), this tool can be useful for creating various types of data. It can generate a set of dates that change according to your ‘Loop Expression’ (DateTimeAdd([date],1,”days”) would increment by one day). I haven’t tried other types apart from numeric and dates but I assume the biggest limitation would be the logic needed (something you just have to figure out).
RegEx can be a helpful tool for extracting specific expressions from a string. In the beginning, it may feel a bit overwhelming but there’s a nice website that highlights the matched expressions within a string. If you want to practice or just see whether your expressions have the expected result, head to regex101.com and give it a go (again, thanks Jonathan). There are a few output methods for RegEx, I have only used Tokenize so far. Maybe once I’m more familiar with it, RegEx will get its own post.
The subjects that I’ve explored so far:
That’s it!
