Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
MacRo
Alteryx
Alteryx

There are a lot of tools in the Alteryx tool palette, but most of us only ever really use a handful of the ~150 tools that come with a full install of the software. The fact is, is that once you get a solid handle on just a few tools, it doesn't take long to see just how much you can do with that subset of tools -- and at that point, is it even worth the effort to explore some of the more obscure tools? Well, yes! Yes it is! There are tools buried in that wild jungle of functionality that will change the way you build workflows forever, and once you pick them up, you'll never want to go back.

 

The Developer toolset can be particularly daunting (Base64 Encoder? Blob what?! I'm not a developer and these tools clearly don't apply to me! Why bother?) -- and in a way, you'd be right -- not all of those tools need to be used by everyone. But there are some absolute gems in there that almost everyone can benefit from. In this post, I'm going to walk you through a simple example to illustrate the awesomeness of the following tools:

  • Block Until Done (in the Developer toolset)
  • Run Command (in the Developer toolset)
  • Dynamic Input (in the Developer toolset)
  • and, as a bonus... Directory (in the In/Out toolset)

This example came out of helping an Alteryx user with the following problem.

 

Each day, a data source I need is updated in a staging folder. I need to copy the data from staging over to a local directory (which can take up to 10 minutes), and then run my Alteryx workflow.

 

I'm going to break the solution up into four parts, walking through a new piece of functionality in each section. The workflow attached to this post contains all four example workflows that you can run on your own machine to see what's going on, but if you want to jump right to the final solution, the Part 4 workflow contains all of the tools mentioned in this post.

 

Part 1: Using the Run Command tool to copy files with Alteryx

 

Alright, let's jump into this and see how we can use the Run Command tool to automate the copying of files, so that you never have to do this by hand again. The Run Command tool allows you to execute commands through the Windows Command Line (you can play around with this yourself by typing "cmd" into the Start Menu search bar). If you're not familiar with the Windows Command Line, that's perfectly fine -- it doesn't take much to make things happen, and you can usually find solutions by googling "how to copy files with windows command line" or something similar.

 

run command.png

In this case, we're going to use the "copy" command like so:

 

copy /Y [source] [destination]

(The "/Y" is an optional parameter that says that if the destination file already exists before the copy takes place, then it's ok to overwrite that file.)

 

So what we're going to do in this workflow is define where the source files are located and where we want them to be copied to, we'll use the Formula tool to generate a command that will copy the files to their destinations, and then finally we'll execute the commands that we've generated. Here's what this simple workflow looks like:

 

Part 1a.png

 

Pretty straightforward right? Take a look at the configuration of relevant tools below (or open up the workflow yourself) to see how its setup. (I split it up like this so that you could ease into things, and didn't want to show a big detail heavy image to introduce the workflow. But going forward, I'll cut to the chase.)

 

Part 1.png

A couple notes about how the Run Command tool is configured (this is useful information if you haven't used the tool before, but greyed out to help the skimmers out there absorb the general concepts quickly!😞

  • Write Source: An output file is specified so that the commands we've created with the Formula tool are written to out as a "batch" file. This is simply a plain text file containing commands to be executed by the command line. Batch files aren't a recognized data type by Alteryx, but you can create a plain text file by selecting the output data type of "csv" and setting the delimeter to "\0" (which represents no delimeter). You can see how its configured in the workflow, but the key point here is that it's just a regular text file that you could create in notepad.
  • Command: Really, you can have the Run Command fire up any executable (/application) that you want. So despite it's name, you actually have to tell the tool that you want to run a command on the command line. For simplicity, you can also just enter "cmd" here, however if you package up your workflow into a .yxzp file, then when someone opens it up, they will see an import error saying "cmd" is not a file that exists in the package. This is totally benign, but can be avoid altogether by specifying the full path to cmd.exe.
  • Command Arguments: The "/c" is necessary here to tell the command line to run what comes afterward. This may be a bit confusing, but as a rule of thumb, when calling the command line, put "/c" before the command. For example, you could put "/c mkdir c:\new_folder" (the mkdir command simply creates a new empty folder).
  • Run options: The "Run Minimized" and "Run Silent" options are set so that all this copying business happens quietly in the background without popping up a distracting Command Prompt window on the screen. (Any issues will still be reported to the logs though.)

 

Now go ahead and run the workflow to become inducted into the Hall of Developer Tool Users! It's a small and prestigious group, but everyone here is friendly and is always happy to help solve problems and share the Alteryx tricks they've discovered.

 

Part 2: Using the Directory tool to identify recently modified files

 

This is great, but I only want to copy files in the Staging directory that have been modified in the past day.

 

Now that you're an expert with the Run Command tool, you might be thinking, "I can find recently modified files with the dir command, which I can then parse out with Alteryx." And sure, that might be a fun little game to play, but you can do this very easily with the Directory tool (not a Developer tool, but still one of the lesser-revered tools in the pallette, and can be found in the In/Out category).

 

directory.png

 

The Directory tool allows us to specify a directory and the file type we're interested in, and will return a list of all files meeting those specifications. It also returns a ton of fields with details about each file, such as the creation date, modification date, folder, filename, whether or not it is a system file, etc.

 

Take a look at the config below. In addition to the Directory config, take a look at how easily you can check how long ago the file was modified with the Formula tool!

 

Part 2.png

 

 

Part 3: Using the Block Until Done tool to do things only after other things have finished running

 

Sweet! But once the files are done copying over, I want to kick off other processes. Can I do that?

 

Yes you can! The Block Until Done tool is one of the most useful ones in the toolset since it gives you so much more control over the flow of how your data is processed.

 

block until done.png

 

The way it works is pretty simple:

  • First the data stream coming out of output #1 will be run
  • Then once #1 is completed, the data stream coming out of output #2 will be run
  • Finally, the data stream coming out of output #3 will be run

Check it out:

Part 3.png

Part 3.5: Let's go on a tangent and take a quick look at runtime events

 

If you're wondering what that flashy business that happens at the start of the Part 3 workflow, here's the answer: It's a runtime event. These can be accessed by going to the Workflow configuration (click anywhere on the canvas and in the Configuration panel, click on the "Events" tab). I'll leave the details for another post, but essentially this lets you run commands in the same way you do with the Run Command tool, except that they will be triggered either immediately before or after the workflow is run.

 

In this case, I added this event because the example depends on the destination files not existing when you run it. So after you run the example once, if it is run a second time, then you would not be able to see the how the Block Until Done tool controls the flow of processing. So we can use a runtime event to delete the destination files at the start of each run, making it so that you can run the workflow over and over and over again... and see the files being copied over each time! (Aw yea!)

 

Here's what that configuration looks like:

 

runtime event.png

 

Part 4: Using the Dynamic Input tool to read in the data after it has been copied

 

Ok that's cool, but I want to actually do stuff with the data once it's been copied over

 

The Input tool is great and all, but don't you ever wish it were more... dynamic??? I did too, before I discovered the Dynamic Input tool. It's pretty great, and allows you to read in files based on filenames contained within a data stream. This is the final key in building out this workflow. Let me show you how it works.

 

 dynamic input.png

 A couple notes about the Dynamic Input tool:

  • You need to give it "template". This is simply letting it know what format the input files will be in, so if you have multiple input file types, it probably makes sense to use more than one Dynamic Input.
  • The template is just like the Input tool's configuration. A useful tip is to set the "Output File Name as Field" option in the template so that you can see which file each record came from.

 Part 4.png

 

The end

 

Well that's all I've got. I hope this is helpful for those who haven't dived deep into Alteryx yet, and maybe even inspires you to explore even more of the tools in the Alteryx pallette. If you haven't already checked it out, Data Prep subforum on the Alteryx Community is a great place to share tips and learn more ways to use the tools included with Alteryx.

Em Roach
Lead Research Scientist

Comments
NeilR
Alteryx Alumni (Retired)

@dataMack One place where we're trying to add examples highlighting how to use individual tools is in the product itself. Clicking on a tool in the toolbar brings up a short description, sometimes with an "Open Example" link below. Currently about 30 of the most-used tools have these examples built in and we're adding more with each release.

 

one-tool.png

NeilR
Alteryx Alumni (Retired)

One more thing: great description of the Run Command tool above, but I recently came across another great description here:

http://community.alteryx.com/t5/Data-Preparation-Blending/Run-a-python-script-in-Alteryx/m-p/10986#M...

dataMack
12 - Quasar
Thanks for the tip on the 'Open Example' from the toolbar pop-up! Hadn't noticed that one before.
AndrewW
11 - Bolide

Great article, only on thing that's not clear to me. With the Dynamic Input tool how do you set the "Output File Name as Field" option? I can't see that option in the dynamic input tool configuration and don't understand how it can be configured in the template, which I thought was just showing Alteryx what columns/column format to expect from each file being input?

MacRo
Alteryx
Alteryx

Hey @AndrewW, if you haven't already, take a look at the example workflow attached to the blog post. The template configuration is really just the same as the Input tool configuration, and so you can set various options such as the input file type, how many records to read in, the delimiter type (depending on file type), and so on. Click on "Edit" next to the template box to set this configuration options -- you should see the "Output File name as Field" in the list here, like in the screenshot below:

 

screenshot.png

mix_pix
10 - Fireball

This is awesome, Mac.  This process has been very helpful to me, and served as the springboard to being able to more fully streamline and automate my workflow.  You're right that once you familiarize yourself with these tools (and they aren't that complicated once you use them a few times), whole new worlds of possibility open up.  I documented my experience with all of this in a recent blog post:

 

http://mixpixviz.blogspot.com/2016/02/one-step-closer-to-alteryx-nirvana.html

 

Thanks again for your help.

 

-Mike

RodL
Alteryx Alumni (Retired)

Mike,

Thanks for sharing the blog you wrote! Very well explained and documented with the screenshots. Ever think of being a "technical writer"? Smiley Wink

Rod

mix_pix
10 - Fireball

Thanks Rod.  I have sometimes flirted with the idea, seeing as that would allow to me make more use of my Lit degree. :-)

 

-Mike

SeanAdams
17 - Castor
17 - Castor

Superb post; well documented; easy to read; progressively explains the concept.

and given that it was written 2.5 years ago, it's stood the test of time!

MacRo
Alteryx
Alteryx

Thanks @SeanAdams, much appreciated! This was really fun to write and I'm stoked that people have found it helpful. Maybe one day I'll get around to writing a followup :)