Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
With Alteryx 5.0 and the new toolbox, there is a new tool folder called: Developer Tools. What is it and why is it called that?
The 1st question that could be asked is: what is an Alteryx developer? An Alteryx developer is someone who develops macros, wizards or modules to be scheduled to be run regularly. They may also be interested in using Alteryx to integrate with other processes. This may be via the AlteryxEngineCmd.exe or the API. The common thread is that they are designing Alteryx modules for a larger group and they have less control of the runtime environment than the typical power user who is designing and running interactively.
The Developer Tools section includes tools that are typically used in these scenarios. Some of them are also useful in an interactive environment and are found in multiple categories.
Block Until Done
What does it do?
It blocks records from passing through until the very last record has arrived. On output, it also ensures that only a single output stream gets records at a time. Subsequent streams will be blocked until all the records are pushed through the 1st.
Why would you use it?
A common scenario is that you are reading and then writing to the same file. This insures that the reading of the file will be closed before it tries to write. You might also use it for writing multiple tables to an Excel file. The Excel file will only let 1 output tool access a single file at a time.
Things to watch out for:
This tool is very simple, there isn't much that can go wrong. It will slow down execution because it needs a bunch of memory, or for large streams a temp file, but if you need it, you need it.
Other Tips:
The output streams will write in the order you create them. If you want to control the order of output, delete the output connections and remake them in the order you need.
Detour & Detour End
What does it do?
These mark 2 sections of the module to be selected at runtime. The detour selects if you are going right or left and only the selected side is run or even evaluated.
Why would you use it?
It is typical in a wizard or macro to turn on or off a whole section of the module based on user input. For instance, a geocoding macro might have an option for CASS coding 1st. This makes it easy to effectively remove any number of tools with a single wizard/macro action.
Things to watch out for:
The unselected side does not get evaluated or run at all. That means in the GUI, meta info (fields and such) is not getting updated for downstream tools. It makes it very hard to configure the disabled side.
Other Tips:
Make sure your wizard or macro sets the direction either way and you don't rely on the state in the module. That way the developer can switch it as needed for testing without breaking the wizard/macro.
Dynamic Input
What does it do?
It reads one or more files or SQL tables. The file name and/or SQL table/query do not have to be known at configuration time.
Why would you use it?
We use this for parsing web server log files. We can choose a date range in a wizard and (using the directory tool) only read files in that date range. You might also use it for fuzzy matching against a really big database. You can use it to select out just the geographies or ZIP codes that might be needed without reading all the data.
Things to watch out for:
Make sure the schema of all the files you are reading is the same. If the schema differs, it causes an error.
Other Tips:
You can dynamically change part of a query fairly easily. If you need more than the simple options in the tool, use a formula tool to remake the entire path or connection string or table and read based on that.
Dynamic Rename
What does it do?
Renames fields without having to know the new (or old) field names at configuration time.
Why would you use it?
It is useful when custom parsing text files. Often the field name will come from the 1st row or another description file that is only known at runtime.
Things to watch out for:
When using a right input for field names, be careful about the record order. A join or similar tool can change the order of records and you wouldn't want to associate the wrong fieldnames with the wrong fields.
Other Tips:
Renaming fields is a handy way of stripping prefixes or suffixes that might appear at runtime.
Dynamic Select
What does it do?
Selects (or de-selects) fields at runtime.
Why would you use it?
You might want to produces a data statistics report differently for different field types or you may want to select fields with a certain prefix for common processing.
Things to watch out for:
It is very easy to end up with a stream with no fields. Most tools can't handle that and it will cause an error. The transpose tool is among the few tools that can handle having no fields on input.
Other Tips:
Look at the data statistics wizard (typically found here: C:\Program Files\SRC\Alteryx5.0\RuntimeData\Wizards\DataStatistics.yxwz) for a good example of usage.
Field Info
What does it do?
Returns the FieldName, Size, Type, Source & Description of the incoming fields.
Why would you use it?
Typically combined with the test tool (and maybe BlockUntilDone) to validate a schema is correct before doing scheduled processing.
Things to watch out for:
It is a simple tool. Not much with go wrong.
Other Tips:
This is one of the few tools that will output records even if there are no records coming in to it.
Run Command
What does it do?
Runs an external process and waits for the results before continuing execution.
Why would you use it?
You need functionality that Alteryx doesn't have. We have used it with curl.exe to download data from web or ftp sites. It has also been used to execute external statistics engines. Some customers use it for wrapping custom functionality written in other programming languages.
Things to watch out for:
If the program you are using doesn't set error flags, you might want to wrap it in a batch file to at least make sure the output file is deleted before it is run.
Other Tips:
If the external program is a UNIX style command line app that takes input from stdin and writes to stdout, you can use the << and >> operators to pipe the information to and from files.
Test
What does it do?
It causes an error if a condition is not met.
Why would you use it?
It is a great way to verify data before you commit a bad update to a database. Often used with modules that are scheduled to run live updates nightly. We use it for the
Alteryx regression tests.
Things to watch out for:
Even if it errors, the module will still keep running, maybe writing bad data out. You often want to set the "Cancel Running Module on Error" option in the advanced module properties.
Other Tips:
Use an event to email you when the module fails. That way when your nightly data update fails, you will know about it.
