PRODUCT: Alteryx Designer and Alteryx Server VERSION: 11.0 LAST UPDATE: 02/15/2017 ATTACHMENT: None Connecting to Oracle in Alteryx 11.0 The Oracle connection has been redesigned for Alteryx 11.0 and it is now much easier to connect! Existing Oracle connections and Aliases will function the same in Alteryx 11.0 as they did in previous versions but there are some differences in how new connections are established. If you have NEVER connected to Oracle before.... (Note: an Internet connection is required for this option) 1. Add an Input tool to the canvas and select Oracle Click the drop down arrow to bring up the connection options. 2. Select a directory to install the client A window will appear prompting you to select a directory to install the client. Alteryx will then download the appropriate client and install it for you. This should take a few minutes. 3. Create a Saved Oracle Database Connection Enter information in the database connection window to create a new Oracle Saved Database Connection. Simply start typing in the Connection Name box to create a new Connection Name you will later use to identify the connection. Your Oracle DB Admin will have to provide the Host, Service Name and Credentials. 4. Test the connection then click OK to connect Errors that start with ORA- and a number, such as the one below, are thrown by the database. Verify that your connection information (host, service name, port, username, password) are correct and that you have access to the Oracle database. 5. Create your query If your connection test was sucsuccessfullick OK to connect to the database and create your query using the Visual Query Builder, Tables Tab or SQL Editor. Note: Stored Procedures are currently not supported for Oracle. 6. Use the Saved Database Connection Going forward, Alteryx will not ask to install the driver again. It will recognize the driver previously installed. If you need to connect to the same database in another input tool, you can find the connection in the drop down option. If you need to connect to a different database, you can repeat steps 3 - 5 for the new database. If you have connected to Oracle before.... ....or if you want to use an existing Oracle client on your machine. 1. Add an Input tool to the canvas and select Oracle Click the drop down arrow to bring up the connection options. Alteryx will search your machine for an existing Oracle client. 2a. If Alteryx can find an existing client: The Oracle Database Connection window will appear. The drop down will reveal Aliases (now called Saved Data Connections) created in previous versions of Alteryx. You can also start typing in the Connection Name window to create a new Saved Data Connection for Oracle. 2b. If Alteryx cannot find an existing client... ... and you have the appropriate permissions on your machine, internet access, don't care if you use an existing driver, and your Oracle DBA did not tell you that you must use a specific client: Follow the instructions for users who have never connected before to install the Oracle Instant Client through Alteryx and use it to connect. Note: This will ignore any clients previously installed on the computer and it should not interfere with them. This is a good option to use if you are currently using a 32 bit client and want to use the 64 bit client for Alteryx. 2c. If Alteryx cannot find an existing client... ...but you have a client you want/must use to connect: Most likely Alteryx can't find the existing client because it couldn't find Oracle Home in the Windows Environmental Variables and/or there was no tnsnames.ora file in the Oracle Home Directory. You can force Alteryx to use the existing client by setting the Environmental Variable and creating a tnsnames.ora file. To do so: - Close all instances of Alteryx - Navigate to the folder containing the Oracle client files - Create a blank text file called tnsnames.ora. Be careful not to save with an extension such as .txt or .csv and save to the folder - Copy the path of the folder - Open up Windows Environmental variables and create a new one called TNS_ADMIN with the path for the Oracle client file folder as the value --> If you already have a tnsnames.ora file, or a TNS_ADMIN variable, do not overwrite any of these - Open Alteryx and try connecting again If you want to connect the "Old" way.... ... or if you want to use a tnsnames.ora file. The new Oracle connection uses the Oracle instant client but it doesn't use any tnsnames.ora files you might have on your computer. If you have multiple entries in the tnsnames.ora file, you would have to create a new Saved Data Connection for each of those entries or you have the option to use the tnsnames.ora file. In the Input tool, navigate to Other Databases and select Oracle OCI (Oracle Call Interface): This will display the screen familiar from previous versions of Alteryx allowing you to enter the TNS Service Name from your tnsnames.ora file. Note: if you set up Aliases in previous versions using the Oracle OCI connection, they will continue to function in Alteryx 11.0 as they did in Alteryx 10.6 unless you edit the connection. If you must use a 32 bit driver.... ... as required by your IT You will have to navigate to Other Databases > 32 bit Database Connections and then select ODBC or OCI as appropriate. In order to use these connections, you will need to have the driver already installed and a tnsnames.ora file created in the Oracle Home directory. If you are using ODBC you have to set up a connection through the Windows ODBC Data Sources Administrator before attempting to connect through Alteryx. To connect using the Oracle Call Interface (OCI), enter the TNS Service Name as it appears in your tnsnames.ora file and the username and password: If Alteryx cannot locate the driver, see step 2c (Alteryx cannot find an existing client but you have a client you want/must use to connect) above.
The Find Replace Tool is one of those tools that goes relatively unused and uncelebrated until you stumble into a data blending technique that would be extremely difficult without it – at which point, it becomes your favorite tool in the Designer. You can find it in the Join Category and it’ll make easy string substitutions in your data that would otherwise require herculean effort to work around. Today, we celebrate Find Replace as a hero.
When it comes to spatial analyses, few tools come up more than the Trade Area Tool. Whether you’re looking to pad polygons around your spatial objects in distance or drive time, you won’t need to make a trade-off - just the Trade Area Tool.
One of the most underrated tool groups, the Documentation Category is full of gems that can help you improve the aesthetic, organization, and shareability of your workflows. Chief among them is the Tool Container Tool – in addition to better aesthetic this tool has the ability to disable all the tools within it, lending itself to a handful of functional applications as well:
Brand new design! Same helpful information. Find the familiar table of contents and search bar by clicking the menu (inside the green rectangle). Getting Started resources include connecting to data sources, building workflows, and learning tools.
If you're using the Formula Tool in v11.0+, be sure to familiarize yourself with the tool's interface redesign! Often, clients try to format a plain numeric value to a currency format. A simple way to accomplish this is to use the optional "addCommas" parameter in the ToString function. This function converts a numeric value to a string, using a user-specified number of decimal places [numDec]. Though Alteryx doesn’t require the third parameter, if called, the third parameter inserts the appropriate commas (1 or true means format with commas). ToString([original_value], 2, 1) Since the new value is a String, simply add in the dollar symbol: '$'+ToString([original_value], 2, 1) Happy Alteryx-ing!
If you're using the Formula Tool in v11.0+, be sure to familiarize yourself with the tool's interface redesign! Alteryx’s spatial functions are not limited to the tools found in the Spatial Palette. The Formula Tool, for example, has a variety of spatial functions that expand the Designer’s spatial capabilities beyond those in the Spatial Tool Palette. This article highlights the Formula Tool’s ability to create spatial objects, particularly points and lines, and calculate distance using the built-in expression library (Figure 1). Figure 1: One of the Formula Tool's expression library contains spatial functions, which can be used to create spatial objects, perform spatial calculations, and retrieve spatial information. For the most part, the spatial functionalities included in the Formula Tool expression library are available in tool in the Spatial Palette. However, rather than dragging on multiple spatial tools to complete a process, using the spatial expressions in a single Formula Tool allows you create a more streamlined workflow. Consider the example workflow that accompanies this article. The input data consists of two sets of Lat/Long coordinates, one for the start point and one for the end point. The objectives of the workflow are to 1) create points for the start and end locations, 2) create a line connecting the start and end locations, and 3) calculate the distance between the two points. Believe it or not, all three of these processes can be accomplished using a single tool! In the attached v10.0 workflow, Workflow A (top) uses four expressions in a Formula Tool (Figure 2A). Workflow B (bottom) uses the spatial tools to accomplish the same task. This approach requires 8 different tools to configure the data, create spatial objects and perform the distance calculations (Figure 2B). Both workflows achieve the same results but, in the spirit of minimalism, the Formula Tool’s ability to handle spatial processes efficiently and effectively certainly deserves some kudos! Figure 2A: Completing spatial functions in a Formula Tool. Figure 2B: Using Spatial tools (and others) to complete the task. Green text boxes indicate which expression(s) in the Formula Tool accomplishes the same task.
If you're using the Formula Tool in v11.0+, be sure to familiarize yourself with the tool's interface redesign! In 1925, the small town of Nome in Alaska suffered a diphtheria outbreak. Out of usable antitoxin and in the depths of winter, the town doctor telegrammed the governor in Juneau and authorities in Washington D.C. requesting a million units of antitoxin to stave off an epidemic. Confronted with the task of saving the people of Nome, Alaskan authorities debated their options of getting the antitoxin to Nome. With sub-zero temperatures, limited daylight and a lack of experienced pilots, airlifting the supplies was a less than ideal solution. Instead, a dogsled relay was selected to transport the antitoxin across the 674 miles from Nenana to Nome. The mushers endured near-blizzard conditions and hurricane-force winds to deliver the serum five and a half days later, saving the small town from a deadly outbreak. While you may not have the fate of a small town on your hands, understanding the time elements in your data is critical for your analyses. Perhaps you need to determine a product’s time in transit between two ports or warehouses, identify non-business days in your dataset to correctly account for time, or re-format data to work on a 12 or 24 hour clock. In all of these cases, time is of the essence! To demonstrate the use of the Formula Tool’s DateTime functions, and in the spirit of the Alaskan dogsledding tradition, we’ll work with data from the last leg of the Iditarod, which stretches from Safety to Nome. Using the data provided from the 2016 race, we will 1) calculate the elapsed time for each musher between the two checkpoints, 2) determine the Iditarod’s start date and time from a musher’s total race time and arrival time in Nome, and 3) re-format the time format from a 24 to 12 hour clock. Before beginning with Formula Tool’s DateTime functions, make sure that your data should be appropriately formatted as a DateTime field type using the DateTime tool or a Select Tool.* *The only acceptable string format for use with DateTime functions is yyyy-mm-dd HH:MM:SS, which is Alteryx’s native string format. The Input data contains the time each musher left Safety (Departure) and the time that they arrived in Nome (Arrival), finishing the race. To calculate the time it took for each musher to complete this final leg of the race, we’ll find the difference between the start (Departure) and end (Arrival) times using the DateTimeDiff function: DateTimeDiff (, , ) [Arrival] [Departure] ‘seconds’ The expression returns the elapsed time between the Arrival and Departure times as years, months, days, hours, minutes or seconds as a numeric field type. In this case, the expression returns the number of seconds (Int64) between the Departure and Arrival times, which can be used downstream to calculate hours (Seconds/3600) and minutes (Seconds/60). The sample dataset not only provides data for the final leg of the 2016 Iditarod but also each musher’s time for the entirety of the race (Total Race Time). Using these pieces of information, we can determine the time at which the mushers started the race in Anchorage by subtracting the musher’s total race time from their completion time (Arrival) in Nome. After a bit of parsing and simple math, we can convert a Total Race Time of 8d 11h 20m 16s to its value of 732016 seconds (Total Seconds). Then, we can subtract the race time (in seconds) from each musher’s arrival time in Nome to determine the start time of the race using the DateTimeAdd function: DateTimeAdd (, , ) [Arrival], -[Total Seconds]*, ‘seconds’ *Because we are looking to determine a DateTime in the past, we use a negative value for the time interval. When looking for a DateTime in the future, ensure that your time interval is a positive numeric value. After applying our DateTimeAdd function, we find that the 2016 Iditarod began at 2016-03-06 15:00:00. That’s not the easiest format to understand, so let’s take advantage of the power of the DateTime Format function. The DateTime Format tool allows you to indicate a format specifier to parse components of a DateTime field. A format specifier begins with a percent sign (%), followed by a case-sensitive letter. For a complete list of supported format specifiers, please refer to the Formula Tool’s help documentation. For our particular example, let’s identify the day of the week, the month, date and time (on a 12 hour clock) that the race started. We use the format identifiers to create a string field that contains this information. It is important to note that each format specifier is treated as text and needs to be wrapped in single quotes (see example below). Using a series of format specifiers, we can see that the 2016 Iditarod began on Sunday, March 6, 2016 at 3:00 PM. When time, whether when dogsledding or managing your business operations, is of the essence, you can count on the DateTime functions to save the day!
Data blending, transformation and cleansing..oh my! Whether you're looking to apply a mathematical formula to your numeric data, perform string operations on your text fields (like removing unwanted characters), or aggregate your spatial data (among many other things!), the Formula Tool is the place to start. With the examples provided below, you should be on your way to harnessing the many functions of the Formula Tool:
Welcome to the 11.0 Formula Tool! While no functionality has been removed from the tool's capability, significant UI changes have been made to this favorite tool in version 11.0. This article outlines some of these changes to help you get ramped up and ready to go!
This new functionality will replace the Alias Manager. The reason for this upgrade is twofold: first, we wanted the ability to create and manage aliases to become part of your database connection process as it will make users life easier when updating passwords and remembering what connections connect to where. The second reason is to have another one of our new functionalities (Database Connection Sharing) seamlessly integrated with users own database connections.
Date/Time data can appear in your data in string formats (text fields) or date formats. The DateTime Tool standardizes and formats such data so that it can be used in expressions and functions from the Formula or Filter Tools (e.g. calculating the number of days that have elapsed since a start date). It can also be used to convert dates in datetime format to strings to use for reporting purposes.
As of the release of version 11.0, the Alteryx Designer supports reading in single and multiple input files in a zipped archive file (.zip). Now, multiple files of the same file type in a zipped archive can be read into the Designer in a standard Input Data Tool.
Now, find all your Server and Gallery questions and answers in one place! The new Gallery Admin Help Page has your Server Installation Guide, Configuration instructions, and the much-requested Administer Gallery management features - Subscriptions and Studios defined! Manage your user permissions! Edit user accounts!
Now everyone loves to talk about their workflow masterpieces, however the notion of documenting them doesn't always hold the same appeal. The Comment Tool would be happy to help and can make documenting and structuring your workflow easy and straight forward. Whether you want to include images, text descriptions or categorize parts of your workflow, the Comment Tool can act as a great refresher when revisiting workflows and make it far more intuitive for work colleagues to follow along. This article provides a few examples on how.