Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
The Scene
It’s a dark night, with a cool breeze whispering through the dry autumn leaves. You’re working on an important Alteryx project for your boss (who really likes pretty visualizations and consistent formatting) and are about to add an output tool to the canvas when you suddenly discover – OH HORROR! – you are stuck with a plain old boring table of data as your final output in Excel!
This will simply not do.
You need some commas, some colors, some headers, some summarizations, some conditional formatting... and to make things more difficult, you also need the output file to include a specific name, like a Vendor name or a date stamp, or a Region.
So you refill your soda and start looking at some solutions:
- Output to a static Excel file, then open the file, copy the data into a formatted workbook manually (or just do the formatting in the original output file)… and then repeat forever. This is a terrible solution, don't do that.
- Build out all of your formatting using the Reporting tools, and then output to Excel. This is challenging, primarily because the Reporting tools are incredibly powerful but also a bit picky when it comes to how you piece them together. So you can take that route, but it's going to be a lot of Unions and Layout tools and possibly some tricky issues with the Render tool and making sure your data all fits into the cells in Excel appropriately... This is trickier than it needs to be. Let’s keep looking.
- You could output to a specific range in an Excel file and then build some sort of fancy VBA script in your file that will format your data and save the file with a new name? But this has challenges as well – first, you have to write that crazy VBA script, and then you have to figure out how to call it from your workflow – do you run a .bat file with Run Command at the end of your workflow, to open the file and execute the macro? This also sounds too tricky, and no one likes VBA anymore.
- You are sure there are some pretty straightforward Python scripts out there that could copy a template file to a new location, and then you could output to specific ranges in that new location. This solution is probably the easiest of the four (oddly, given that it requires programming in Python in order to do it). For the Alteryx purists out there, this can feel a bit like cheating... so it's an option, particularly if you're already familiar with Python and have those scripts in your back pocket already. Still, it can’t possibly be the only option.
So how do you get from a crazy manual solution/macro/code to a nice, simple, happy solution where all the townspeople can live happily ever after with their nicely formatted output files?
Source: imdb
The Plot
Now the IMDB teaser for the film “The Blob” says this movie is about "An alien lifeform that consumes everything in its path as it grows and grows." Not particularly encouraging for solving your reporting problems... but this is where we flip things around a little bit. In our Alteryx scenario, these Blob tools do consume things when you use the Input variety... but they also transform things and output things, and they do so in a friendly, non-destructive sort of way!
First, let's talk about "What is a blob?" Steve McQueen was probably battling red Jell-O in that 1958 film, but from a data perspective, a blob is just a big amorphous chunk of data, which can represent a variety of formats. A blob could be a file, an image, an audio clip, or a video – any multimedia file or object can be classified as a blob from a data perspective. “Blob” stands for Binary Large Objects, and it acts as a way to collect information stored as a single entity in a database, or in our case, in a record in a workflow.
Alteryx has three tools in the Developer category for working with Blobs:
Blob Input is how you can read an image or media file into your workflow to be contained in a single record and used elsewhere in your flow.
Blob Convert is how you could take data from your workflow and convert it into a Blob data type, or vice versa (we're not going to use this one for our specific use case, but it’s still cool).
Blob Output is how you can write a record with your Blob of data to an output file.
Now that we are armed with our Blob tools, how do we put them together?
- Create your template output Excel file with your formatting, formulas, etc. You can have pivot tables, macros, conditional formatting, and multiple tabs… sky is the limit. Make it pretty! But keep in mind that it needs to be consistent, too, because you'll have to tell your workflow exactly where the data needs to be output, so consistency in your output file will be key.
- Input your formatted template file into your Alteryx workflow using the Blob Input tool. A simple example would be a single template input to the workflow, which will then appear as a single record with the Blob data type that contains all the data in your template file. A more sophisticated example, however, could mean that you have different templates for different scenarios, and you may want to choose which template to input based on data from your workflow – in this case, you can optionally choose to use the input node for the Blob Input tool, and then dynamically determine which template to input.
- If your output file needs to be dynamically named, whether that's sending it to a specific file path, appending a DateTime stamp to the name, etc., you can dynamically update your ultimate output file name before sending it to the Blob Output tool.
- Configure the Blob Output tool to output a copy of your template file, currently sitting in Blob form in a record in your workflow, to your desired location with its dynamic filename.
- If you're then going to write to that file with additional data from your workflow, you might want to consider using a Block Until Done or a Parallel Block Until Done tool (from the CReW macro pack) just to make sure your formatted output file is fully created before you start trying to write data to it.
- Finally, the key to finishing the process: use the "Output to Range" option in your Output tool at the end of your workflow to write data to a specific range in your formatted output file. Make sure you check that box to "Preserve Formatting on Overwrite" so that it keeps all that pretty formatting you worked so **bleep** in your output file!
The Conclusion
Cue the dramatic music! You now have a workflow that will give you a dynamically named output file with all the formatting bells and whistles applied.
Probably could win an Academy Award with this one!
In the sample workflow attached, you will see a simple process that takes a formatted Excel template file, creates a new file with it that includes the user's name, and then outputs additional information to specific cells in the new file.
Much like their Hollywood counterpart, the Blob tools are simply “Indescribable… Indestructible… Nothing can stop it!” So you better just sit back and enjoy that nicely formatted output file!
