This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
To go along with our example on how to download a file from FTP, we’ve assembled steps in v10.1 below (credentials, server removed) as an example of uploading a file to FTP. In this example (attached) I’ve encoded a string field as a Blob to be posted as a text file. Theoretically, all your fields could be concatenated to a CSV format, or another delimited format, to be converted and posted using the same steps:
My field string to be converted:
1. First identify the field to be converted to Blob in your Blob Convert Tool:
2. Specify in a Formula Tool your FTP URL and filename in the format URL/filename.extension:
3. Have your Download Tool use this field as the URL field in the Basic Tab:
4. In the Payload tab specify the HTTP action PUT and select the option “Take Query String/Body from Field” and specify your Blob field:
5. Specify your credentials in the Connection tab of the Download Tool, leave all other configuration options default:
6. Run the workflow!
After running, you should be able to confirm the successful transfer of your file in the DownloadHeader field returned from the Download Tool (it'll also be hosted on your FTP path):
Take a look at the results below:
As users collaborate, the sharing of workflows that connect to databases can be helpful. The In-DB Connection File type allows for this. A database connection is saved as an .INDBC file so it can be packaged with a workflow.
To create an In-DB Connection File that can be used on your workflow, go to Options > Advanced Options > Alias Manager > In-DB Connections.
Select the Data Source from the dropdown (1), for our example we will select Oracle, then pick File for the connection type (2), this option will ask you to navigate to a folder where the INDBC file will be stored and also will ask you to provide a name for this file (3).
Then proceed to set up your connection as you would normally do by selecting the appropriate driver and setting up the connection string for Read & Write.
Now that your connection file is created, the next step is to connect to your database using this file. Bring a Connect In-DB into your canvas, click on the dropdown for the Connection name and select Open File Connections, browse to your file and select it.
The next thing you will see the list of tables.
Select the table and/or write your query in the SQL Editor and proceed to build your workflow.
Now that you are ready to share, package your workflow by going to Options > Export workflow, notice that the INDBC file is being packaged with the workflow.
At the receiving end, the database drivers/client, data sources for that database should be installed and set up. The INDBC file looks like below, the password is encrypted.
Note: Workflow and all images created in this article are from Alteryx version 10.1
Need more information on how to use our visual query builder when choosing tables or queries to input? We’ve put together a quick video in v10.1 for your reference:
Find more information on our visual query builder below:
When writing to Excel, have you ever had the need to populate a column with values that come from formulas referencing other cells in your data? You’ve probably noticed that when writing to either .xlsx or .xls Excel file formats that your output still appears as a string, rather than a calculated value, like the below:
Each formula cell has the proper cell references and syntax but will need to be interacted with (add or remove a space in the formula and hit enter on your keyboard) to calculate the true formula value.
When expanding on the string you’ll see there’s an apostrophe (‘) preceding the formula and you’ll need to remove each and hit enter on your keyboard for the formula to execute.
In the attached v10.1 workflow, Writing to Excel with Formulas.yxmd, we provide a sample formula expression to build formulas with basic cell indexing (using a record ID field) and the ability to write formulas that can be interpreted by Excel. This is done by leveraging the .csv file format; when excel opens this output file type it automatically recognizes the formula syntax and will interpret it as such – populating each cell with the true formula calculated value:
Tired of pulling conversion rates manually? Me too!
Let’s build ourselves a currency converter in Alteryx Designer version 10.1 using the http://fixer.io/ exchange rates. Fixer.io is a free JSON API for current foreign exchange rates published by the European Central Bank. The rates are updated daily around 3PM CET.
The first thing we will do is gather the API documentation from the Fixer.io website. This will give us the URL we need to make our REST API call. If you are unfamiliar with making REST calls please take a look at Tasha’s
For 10.1, we updated our installer screens to something a bit more modern looking. Here's a comparison:
Some users may run into an issue where the new Alteryx installer screens do not display text correctly if Alteryx is being installed on a machine using a Windows Classic/Windows Standard theme. (See the image to the right.)
Windows 2008 R2 Server OS system uses this theme, so users with this OS may encounter the issue.
We believe this problem will only occur if the machine also has a previous version of Alteryx installed.
If the issue occurs, there is a simple work-around:
Quit the installation
Uninstall the previous version of Alteryx using the Control Panel.
After the uninstall is complete, run the installer again. The installer screens should display properly.
This information is also available on our Release Notes page (http://downloads.alteryx.com/). It's always a good idea to read through the Release Notes when downloading a new version.
Key Features - highlights exciting new enhancements added for the release.
Additional Changes - lists minor updates as well as notable bug fixes.
Known Issues - documents small issues (like the one outlined above) and work-arounds to use if you encounter them.
Although 10.1 is a minor release, it contains a number of exciting new enhancements, and we're looking forward to hearing what you think about them.
With the introduction of the Predictive Analytics Starter Kit , you can enhance your analytic skills through an interactive, guided starter kit that teaches core predictive modeling techniques (A/B testing, linear regression, and logistic regression)
How to dynamically run the most recent file in a file folder
Sometimes you may have daily, weekly, monthly or yearly data dumps where you want to only run the most recent file. Within Alteryx you can make this process dynamic and seamless through the use of a few tools.
Step 1: Directory Tool
The Directory Tool will allow you to browse to a folder and return all the metadata related to the files which exist within that folder. The field of interest in the metadata is the 'Creation Time'.
Step 2: Sort Tool
Using the field called ‘Creation Time’ we can use the Sort Tool to sort the date and time values into descending order to get the most recent file at the top of the dataset.
Step 3: Sample Tool
After sorting the 'Creation Time' field I now have my most recent file in record 1. Yet, I still have rows of data for the other files within that folder that I need to remove. I can now use the Sample Tool to take the ‘First 1 Record’ and this will result in the latest file information being left.
Step 4: Dynamic Input
Currently the field I have in my dataset only shows metadata avaliable for that file such as Full path, Creation Time etc. I now need to read this file and pull in the data by using the Dynamic Input Tool. In the ‘edit’ section select a placeholder file.
Then in the Read a list of data sources ‘Field’ dropdown this will need to be the ‘Full Path’ field coming from the directory Tool. In the Action dropdown this will need to be set to ‘Change Entire File Path’.
Step 5: Run the workflow
You can now run the workflow and it will dynamically always pick the latest file from that folder and read the data into Alteryx.
Please find an example module attached to this article (Built in Alteryx Designer 10.5)
So you've built out your app and everything is working great, provided the user enters in all the information requested by your well thought out and organized user interface. How do you protect yourself from a flood of emails due to user error?
With the addition of a simple to configure tool, you can easily add checks and custom error messages if things are not entered correctly.
The Error Message Tool (Interface Toolset) is simple to configure and can provide the insurance you need to make sure that the information requested by your Analytic App is being entered correctly.
The Error Message tool connects to questions and allows you to build out a custom expression to ensure the data entered is what you're looking for. One of the most common uses is simply to make sure something was entered. To do this, connect your Error message tool to any question in your app and fill out the configuration as shown below:
When I run my app, and leave the answer to that question blank, my error appears:
You can also use a single Error Message tool for multiple questions. Instead of building a separate message for each user required input, you can connect additional questions into a single Error and return an error if any of them are left unanswered:
You'll notice the questions are represented by [#1] and [#2] in the expression. This corresponds to the numbered connection line feeding into the Error Message tool. A best practice is to rename those lines so that you know which connection applies to each question. The process of renaming is simple. With any tool configuration window open, click on the connection line you wish to rename and enter the name you want to change it to. You'll see the change on the workflow canvas itself, as well as within the Error Message tool:
Now if a user leaves either one of the questions blank an error will appear stating that he/she must answer all questions.
A sample app demonstrating the setup is attached.
Note: Sample App and all screen shots taken from 10.1
You may be trying to install the Alteryx Designer or Server and recieve an error saying: There was an error downloading "AlteryxInstallx64_10.1.7.12.188.exe...." (Image below)
This is due to a proxy blocking the data manager from installing the software.
DO NOT FEAR THE ANSWER IS HERE!
All you need to do is to install the Individual Installs of the Designer/Server/Predictive tools to circumvent the data manager GUI and you will be able to download the designer as normal.
*****Please remember to download the correct bit and admin/non-admin version for your machine!
Once the download has finished it will prompt you to finish and open the designer. This is when you can enter your license key and use Alteryx!
If this fails to resolve your issues please do not hesitate to reach out to Support
Client Services Support Engineer
Ever wonder about the “behind-the-scenes” calculations of Alteryx’s drive times? How does Alteryx know how long it would take to travel from Point A to Point B? This post explores the “speed” variable used to calculate Drivetimes.
In Designer v10.1, there are four components to making Salesforce connections in the Designer Salesforce tools:
Making sure you have the correct Salesforce URL; it would look something like https://[instance].salesforce.com. For an example, I've posted one that I use below:
Your Salesforce username
Your Salesforce password
A Security token that you can recover from your Salesforce administrator or using the steps below (if you have the sufficient privileges from your Salesforce admin): In Salesforce.com, click on the picture icon in the top right hand corner and click on my Settings Click on Personal > Reset My Security Token
This will send out an automated email with your security token. Voila! You should be ready to connect!
If you have trouble connecting thereafter, please consult our help pages or the Client Services team!
Azure ML Text Analytics Macro
This API has now been depreceated by Microsoft and has been replaced by the Microsoft Cognitive Text Analytics Macro API.
The collaboration between Alteryx and Microsoft is growing and the ability to take advantage of Azure Machine Learning is becoming increasing popular! The Azure ML Text Analytics macro makes this process easy and seemless without having to battle lines of code to perform Sentiment Analysis and Key Phrase extraction.
To use this macro you will need to create a Microsoft Account, as well as signing up to the Microsoft Azure Marketplace. Within the Azure Marketplace you can choose from the various different subscriptions depending upon the amount of text analytics you want to run. Once you have chosen your subscription if you go to 'My Account' it will give your 'Primary Account Key' which is what you will need to insert into the Azure ML text analytics macro to make it run.
Once you have inserted the Account Key you can select the field you wish to run the Text Analyisis on and then choose the type of Analysis.
To find out more about the types of Analysis and the Azure Machine learning please sign up to Cortana Analytics and this will give you additional information on the machine learning API used by the Azure ML Text analytics macro.
Again if you have any issues with the tool please do not hesitate to reach out to Support
Client Services Support Engineer
Starting in 10.1, Alteryx added the ability to connect with MIT Kerberos based Impala implementations. With this feature, came the need to tell the Simba Impala driver that you are using Windows Kerberos and not MIT's version. The typical sign that you need to enable this option is this error:
[Simba][ImpalaODBC] (100) Error from the Impala Thrift API: SASL(-1): generic failure: GSSAPI Error: Unspecified GSS failure. Minor code may provide more information.
Make sure you have the latest Simba Impala driver for the next steps. To enable the Windows Kerberos in the driver:
Open the ODBC Administrator
Select the User or System Impala DSN that you have configured and press Configure
Once in the Simba Impala ODBC Driver DSN Setup window, press on Advanced Options
Under Advanced Options, check the Enable for this DSN for the Use Only SSPI
Press OK on all the windows, start Alteryx and test your connection.
If you still can’t get the connection to work, please email email@example.com and one of us will reach out to you.
Client Services, UK.
Error: "system.data.oracle client requires 8.1.7 or greater"
Please bear in mind this error is not related to Alteryx but to Oracle. This error message is returned from the Oracle Database, therefore please check with your DBA about the configuration and set up of your machine and server before reaching out to firstname.lastname@example.org. However, we are always happy to help so here are a few troubleshooting tips!
Which database connection did you use?
If you tried using the ODBC connection, try using the OCI (New Oracle Connection). If you do not know what the TNS server name it can be found in the TNSnames.ORA file. Your DBA will also know this.
What permissions do you have?
Go to the Oracle Client folder.
2- Right Click on the folder.
3- On security Tab, Add “Authenticated Users” and give this account Read & Execute permission.
4- Apply this security for all folders, Subfolders and Files (IMPORTANT).
5- Don’t Forget to REBOOT your Machine; if you forgot to do this you will still face the same problem unless you restart your machine.
*Before changing any permission settings please check with your IT
How many instances of Oracle do you have installed?
Oracle may find it difficult to navigate and select the correct version if it has more than one version installed. If you only need one of them it is better just to remove them all and reinstall the one that is needed.
Do you have the correct drivers installed?
One way to check to see if you have the right drivers installed is to set up the DSN connection through Alteryx, navigate to the ODBC admin and see if any drivers populate for either the 32 or 64 bit connections.
Do you have one of the supported client versions?
Please see the Technical Specifications
Please bear in mind often this error is not related to Alteryx but to Oracle. This error message is returned from the Oracle Datbase side and therefore please check with your DBA about the configuration and set up of your client before reaching out to Alteryx Support
Client Service Support Engineer
Building out a workflow and find yourself stumped when trying to add needed functionality to your process? While the Designer does an incredible job of packaging just about every operation an analyst could need, you might need that extra mile. We get that. Here at Alteryx we are all about going that extra mile; if we don’t have a tool that doesn’t explicitly capture functionality for you, we try to equip you with tools that can make for an easy reach to that functionality from resources just outside of the Designer environment (see our R Tool, the API based Connector Tools, and the Run Command Tool). In this article we’ll go over an introduction on how to make and use batch files – these will easily incorporate command line based scripting into your workflows that will help you do just about everything short of feeding your dog.
In short, a batch file is a plain text file that lists a series of commands for the command line interpreter to run in Windows. They’re frequently used to make, remove, rename, move, or even copy directories or files, ping IP addresses, run other programs or services, and manipulate environment variables – and that’s hardly even the tip of the iceberg. There’s a wealth of resources online listing the different batch commands available to you, but the Ben/Peter Parker rule applies to them all: “with great power comes great responsibility.” Please use them responsibly!
To make a batch file, all you need to do is take your desired batch command(s) and write them into a text file:
Be sure to take note of the paths being used! This batch command will make a directory named “batchfolder” in whatever directory it is in when run. Before it can be run as a script, however, we have to save it as a .bat file:
In “Save As” change the “Save as type” to “All Files (*.*)” and change the “File name” .txt extension to .bat:
In your working directory you should now see a different icon, extension, and type, describing the file:
How easy was that? If you want, you can test to see if it works by navigating to the directory the batch file resides in and typing its name into the Command Prompt:
You’ll now see the directory we made with the execution of the batch file:
See, I’m not making this stuff up. Now let’s get all this set up to run for us in the designer. All you need to do is specify the .bat file name in the “Command” configuration option - by default, it will look for this file in the directory the workflow is saved in:
In my v10.1 example (attached) I use the Run Command Tool as an input of a test file (specified in the “Read Results”) before writing the file to the new directory made from executing the .bat file.
‘Alteryx has stopped working’ - APPCRASH
Scenario: You are running a workflow and all of a sudden Alteryx becomes unresponsive and a windows prompt opens and says ‘Alteryx has stopped working’ with options to close the program. Once you click on problem signature properties it references 'Problem Event Name: APPCRASH'.
This error can be due to several factors however there a few troubleshooting steps which have resolved this error for several of our clients in the past.
Are you on the latest version of Alteryx?
If possible being on the latest version of Alteryx can help ensure any known bug fixes have been resolved and the software is up to date.To find the latest version of the Designer please click on this link Downloads
Limit the number of Browse tools
Every time you add a Browse Tool to the canvas it caches the data locally, therefore if you have a large workflow this can sometimes cause memory issues on your machine (dependent upon your machine environment). This can be adjusted by changing the default sort/join memory, however taking advantage of the in-browse feature in 10.1 is highly recommended!
Before removing all browses you can also 'Disable All Browse Tools' in the runtime configuration settings to test this theory.
Have you tried the block until done tool?
When Alteryx becomes unresponsive it can be due to constraints on the processing power of the machine. More often than not you may receive an error relating to an ‘inbound pipe error’ (also memory related). The Block Until Done Tool (found in the developer category) allows all the pre-processing to occur before passing data downstream (e.g. writing out through an output tool). This often makes it an easier process when writing out to a database.
Adjust your sort/join memory
The sort/join memory is one of the most underestimated elements of the designer. Because Alteryx is designed to use all the resources it possibly can, it tries to balance and use as much CPU, memory and disk I/O as possible. There are great tips on how to achieve the right balance here: http://community.alteryx.com/t5/Alteryx-Knowledge-Base/What-is-the-Sort-Join-Memory-Setting/ta-p/1302
Again, as always if this error persists after trying these troubleshooting steps, please do not hesitate to reach out at email@example.com
Jordan Barker | Alteryx Inc. | Client Services Support Engineer