cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

Inside you'll find the solution to access files residing in Azure Blob Storage using Alteryx Designer!
View full article
Question How do I capture values that are not recognized as dates?  I am importing data from an Excel file.  Two of the fields are supposed to contain dates, but because the values are entered by hand sometimes they aren't entered in a way that is recognizable by Alteryx as a date.  For example, "11/8/`6" is entered instead of "11/8/16".  I want to divert the records that could not be read into a separate workflow. Answer Convert the date field first.  If the date does not convert, a NULL is produced.  Use a Filter Tool based on finding NULLs to create a separate stream:       See v10.5 workflow attached.
View full article
At the time this article is being written, the WebHDFS protocol does not support HDFS high availability Hadoop cluster. This article will change that!
View full article
This is the second article in a series on Alteryx Server backup and recovery. You can find Part 1 at:   Alteryx Server Backup & Recovery Part 1: Best Practices   As long as a backup of the Mongo database is available, you can get Alteryx Server back up and running. Luckily, backing up the embedded MongoDB is pretty simple, and can be done with a few console commands. I would recommend creating a batch file or script to perform the process. Doing so will allow you to schedule the backup using Windows Task Scheduler. The actual steps to perform a MongoDB backup are covered in detail in our Alteryx Server Installation and Configuration Guide in Chapter 8. I will also outline the steps below for completeness.   To create a backup of the MongoDB:   Stop AlteryxService. Execute the following command to save a backup of the database in the specified folder:   alteryxservice emongodump=<path to backup location> Restart AlteryxService   You can easily script this to a batch file with a few simple console commands. Keep in mind that paths may vary on your server, but it should look something like this.   Example:     net stop AlteryxService "C:\Program Files\Alteryx\bin\AlteryxService.exe" emongodump=Z:\Path\MongoBackup net start AlteryxService     You can add additional features, such as logging and date/time stamps, to the backups.  As an example of additional useful features to include with your backups, I have included the code for a batch script I created that adds the following information: logging with date/time stamping, a backup that is also date/time stamped, automated archival of the backup, copying the archive to a network location, and cleanup of the temp files.   Once you have a batch file or other script to perform your backups, you need to test the script to ensure it works properly. Once testing is done, the next step is to schedule the backup. The easiest way to do this is to use Windows Task Scheduler. To create a scheduled task on Windows 2012 Server, follow these steps:   Create a scheduled task:   Open Task Scheduler and click on “Create Task”   On the General tab, enter “Name”, “Description”, and select “Run” whether user is logged in or not   On the Triggers tab, click “New”   A dialogue box will appear. Define the schedule (daily, weekly, etc...) on which you want the backup to run and click “OK”   On the Actions tab click “New”   On the dialogue window, make sure “Start a Program” is selected and click “Browse”. Select the batch file you created and click “Open”. Then click “OK”.   Click “OK” on the Create Task window to finalize the creation of the backup task.   Now that you have successfully implemented backup procedures and scheduled a task to automate the backups, it is time to discuss database restoration from a backup. The good news is that restoring the database is just as simple as backing it up. Assuming that 1) the server is functioning, 2) Alteryx Server is installed, and 3) you have a valid backup available, you can follow these simple steps outlined below.   To restore a backup of the MongoDB:   Stop AlteryxService Execute the following command to restore the backup:   alteryxservice emongorestore=<path to backup location>,<path to restore to>   Restart AlteryxService   This simplicity and same focus on command line statements means that we can also script recovery. However, since recovery actions are much less frequent, it probably isn't necessary. Instead, you would just connect to the server, open a command prompt and, following our backup example above, execute the following commands:   Example:     net stop AlteryxService "C:\Program Files\Alteryx\bin\AlteryxService.exe" emongorestore=Z:\Path\MongoBackup,C:\ProgramData\Alteryx\Service\Persistence\MongoDB net start AlteryxService     For Alteryx Server we also recommend backing up some settings files. While the server can be recovered with these files having a backup of them can expidite the recovery process. The settings files we recommend backing up are:   C:\ProgramData\Alteryx\RuntimeSettings.xml C:\ProgramData\Alteryx\Engine\SystemAlias.xml C:\ProgramData\Alteryx\Engine\SystemConnections.xml   Again, please keep in mind the exact paths may vary depending on the server configuration and where the backup is located. This example also assumes the backup isn't compressed/archived. If you are using a backup script that archives the backup and copies it to network storage, you will need to copy the backup file to the server and decompress the archive before running the recovery commands above.     Below is the code for my sample batch script:   ::----------------------------------------------------------------------------- :: :: AlteryxServer Backup Script v.1 - 5/25/16 :: Created By: Kevin Powney :: ::----------------------------------------------------------------------------- @echo off ::----------------------------------------------------------------------------- :: Set variables for Log and Temp directories ::----------------------------------------------------------------------------- SET LogDir=C:\ProgramData\Alteryx\ SET TempDir=C:\Temp\ ::----------------------------------------------------------------------------- :: Set Date/Time to a usable format and create log ::----------------------------------------------------------------------------- FOR /f %%a IN ('WMIC OS GET LocalDateTime ^| FIND "."') DO SET DTS=%%a SET DateTime=%DTS:~0,4%%DTS:~4,2%%DTS:~6,2%_%DTS:~8,2%%DTS:~10,2%%DTS:~12,2% echo %date% %time%: Starting backup process > %LogDir%BackupLog%datetime%.log echo. >> %LogDir%BackupLog%datetime%.log ::----------------------------------------------------------------------------- :: Stop Alteryx Service ::----------------------------------------------------------------------------- echo %date% %time%: Stopping Alteryx Service >> %LogDir%BackupLog%datetime%.log echo. >> %LogDir%BackupLog%datetime%.log net stop AlteryxService >> %LogDir%BackupLog%datetime%.log ::----------------------------------------------------------------------------- :: Backup MongoDB to local temp directory. ::----------------------------------------------------------------------------- echo %date% %time%: Starting MongoDB Backup >> %LogDir%BackupLog%datetime%.log echo. >> %LogDir%BackupLog%datetime%.log "C:\Program Files\Alteryx\bin\AlteryxService.exe" emongodump=%TempDir%ServerBackup_%datetime%\Mongo >> %LogDir%BackupLog%datetime%.log ::----------------------------------------------------------------------------- :: Backup MongoDB to local temp directory. ::----------------------------------------------------------------------------- echo. >> %LogDir%BackupLog%datetime%.log echo %date% %time%: Backing up settings, connections, and aliases >> %LogDir%BackupLog%datetime%.log echo. >> %LogDir%BackupLog%datetime%.log copy C:\ProgramData\Alteryx\RuntimeSettings.xml %TempDir%ServerBackup_%datetime%\RuntimeSettings.xml copy C:\ProgramData\Alteryx\Engine\SystemAlias.xml %TempDir%ServerBackup_%datetime%\SystemAlias.xml copy C:\ProgramData\Alteryx\Engine\SystemConnections.xml %TempDir%ServerBackup_%datetime%\SystemConnections.xml ::----------------------------------------------------------------------------- :: Restart Alteryx Service ::----------------------------------------------------------------------------- echo %date% %time%: Restarting Alteryx Service >> %LogDir%BackupLog%datetime%.log echo. >> %LogDir%BackupLog%datetime%.log net start AlteryxService >> %LogDir%BackupLog%datetime%.log ::----------------------------------------------------------------------------- :: This section compresses the backup to a single zip archive :: :: Please note the command below requires 7-Zip to be installed on the server. :: You can download 7-Zip from http://www.7-zip.org/ or change the command to :: use the zip utility of your choice. ::----------------------------------------------------------------------------- echo %date% %time%: Archiving backup >> %LogDir%BackupLog%datetime%.log "c:\Program Files\7-Zip\7z.exe" a %TempDir%ServerBackup_%datetime%.7z %TempDir%ServerBackup_%datetime% >> %LogDir%BackupLog%datetime%.log ::----------------------------------------------------------------------------- :: Move zip archive to network storage location and cleanup local files ::----------------------------------------------------------------------------- echo. >> %LogDir%BackupLog%datetime%.log echo %date% %time%: Moving archive to network storage >> %LogDir%BackupLog%datetime%.log echo. >> %LogDir%BackupLog%datetime%.log :: Be sure to update the UNC path for the network location to copy the file to. copy %TempDir%ServerBackup_%datetime%.7z \\ServerName\SharePath\ServerBackup_%datetime%.7z >> %LogDir%BackupLog%datetime%.log del %TempDir%ServerBackup_%datetime%.7z >> %LogDir%BackupLog%datetime%.log rmdir /S /Q %TempDir%ServerBackup_%datetime% >> %LogDir%BackupLog%datetime%.log ::----------------------------------------------------------------------------- :: Done ::----------------------------------------------------------------------------- echo. >> %LogDir%BackupLog%datetime%.log echo %date% %time%: Backup process completed >> %LogDir%BackupLog%datetime%.log
View full article
This post is part of the "Guide to Creating Your Own R-Based Macro" series.   The workflow is now ready to be converted into a macro. To do this, click on the canvas and then on the Workflow tab of the Workflow - Configuration menu, click on the Macro radio button to convert the workflow into a Standard Macro. At this point you will want to use the drop down menu option File > Save as... to save the file to yxmc format. My original workflow was saved to the file Entropy_Importance.yxmd, and I saved the macro to the file Entropy_Importance.yxmc.   We are now ready to add the user interface elements to the macro, and make several other changes. Figure 1 shows the final version of the basic macro.   Figure 1: The basic macro As the figure suggests, the major changes are the addition of a number of interface tools, the Text Input tool has been converted to a Macro Input tool, and a Macro Output tool has replaced the Browse tool of the original workflow. All of these tools fall under the Interface tool group.   I won't go into great detail, but I do want to give an overview of what is going on with the interface tools in the macro. Starting from the top left of the canvas, the first interface tool is a Drop Down tool that allows the user to select the target variable for the analysis. Inside, it is configured to only allow string type fields (which are converted to categorical variables in R) to be selected. The Action tool that it connects to modifies the upper Select tool to filter out all fields except the target field.   Moving to the right, the List Box tool allows the user to select a set of predictors. Within the tools configuration, only numeric variables (various integer, float, fixed decimal, and double types) are allowed to appear in the user interface. The Action tool associated with it modifies the lower Select tool based on the user's selection.   The final three tools as you move to the right in the canvas are Check Box tools, which if checked indicates whether a particular measure will be calculated. As you may have guessed, the macro itself will not only provide the information gain measure, but also the option of including the gain ratio, and symmetrical uncertainty entropy based measures as well.   Given the above, the code within the R tool (provided below) has gone through some alterations to allow for this additional functionality. In addition, the code example also illustrates how the user's input to the Check Box tools can be used as "question constants" in an R tool's code: # Load the FSelector package suppressWarnings(library(FSelector)) # Read in the data from Alteryx into R the_data <- read.Alteryx("#1") # Create a string of the potential predictors seperated by plus signs the_preds <- paste(names(the_data)[-1], collapse = " + ") # Get the name of the target field the_target <- names(the_data)[1] # Create a formula expression from the names of the target and predictors the_form <- as.formula(paste(the_target, the_preds, sep = " ~ ")) # Initialize the output data frame the_output <- data.frame(Field = names(the_data[-1])) col_names <- "Field" # Calculate the entropy based measure(s) selected by the user # via the "questions constants" if ('%Question.info.gain%' == "True") {     out <- information.gain(the_form, the_data)     the_output <- cbind(the_output, out[[1]])     col_names <- c(col_names, "Information Gain") } if ('%Question.gain.ratio%' == "True") {     out <- gain.ratio(the_form, the_data)     the_output <- cbind(the_output, out[[1]])     col_names <- c(col_names, "Gain Ratio") } if ('%Question.symm.uncertainty%' == "True") {     out <- symmetrical.uncertainty(the_form, the_data)     the_output <- cbind(the_output, out[[1]])     col_names <- c(col_names, "Symmetrical Uncertainty") } # Prepare the final output names(the_output) <- col_names # Output the results write.Alteryx(the_output)   It is now time to test to see if the basic macro works as expected in a workflow using different data. For the test workflow I decided to work with the Bank Marketing dataset from the UC Irvine Machine Learning Archive. The full dataset was used, which comes in CSV file format. As a result, the Auto Field tool was used to set appropriate field types. In addition, one of the predictor fields (pdays) is the number of days since a prospective customer was previously contacted with an offer to invest in a term savings account. Those who were never contacted for this product were given a code -1. Given this, the data is separated into those who have, and who have not, received a past telemarketing offer for a term savings account using a Filter tool. Finally, the basic macro was inserted into the workflow twice (based on right-clicking on the canvas and inserting the macro twice), and used against both of the data streams coming from the filter tool, with a Browse tool attached to both of them. The completed version of the test workflow is shown in Figure 2.   Figure 2: Test workflow Frequently, things will work as expected in the workflow contained in the macro, but not when the macro is used in a new workflow, and the test workflow should allow you to find any major errors in your macro.
View full article
Connect to Hive in Azure HDInsight  using the Simba ODBC Hive Driver!
View full article
Please find enclosed the French intelligence database for Alteryx employee movement worldwide.
View full article
Full details on the addins can be found on my   blog; the addins can be download from   GitHub. Hopefully it will make creating some formulas easier!
View full article
Alteryx is designed to use all of the resources it possibly can. In order to make Alteryx run as fast as possible, it tries to balance the use of as much CPU, memory, and disk I/O as possible.   The good news is that most of the resource utilization can be controlled. You can limit the amount of memory that is used on a system, user, or module level.   The Sort/Join memory setting is not a maximum memory usage setting; it’s more like a minimum. One part of Alteryx (sorts) that benefits from having a big chunk of memory will take that entire amount right from the start. It will be split between all the sorts in your module, but other tools will still use memory outside that sort/join block.  Some of them (e.g. drive times with a long maximum time) can use a lot.   If a sorting can be done entirely in memory, it will go faster than if we have to fall back to temp files, so that’s why it’s good to set this higher.  But if the total memory usage on the system pushes it into virtual memory, you’ll be swapping data to disk in a much less optimal way, and performance will be much worse and that’s why setting it too high is a bigger concern.   The Default Dedicated Sort/Join Memory Usage can be found in the Designer at Options > User Settings > Edit User Settings Best Practices on Memory Settings 32-bit machines*: Setting should be on the lower, conservative side. No matter how much actual RAM is there, only has at maximum 1 GB available, as soon as it is set higher, the machine will cross over into virtual memory and be unable to recover.   A 32-bit machine should never have a setting over 1000MB, and 512 is a good setting. Set it low (128 MB), especially when using Adobe products simultaneously with Alteryx. 64-bit machines: Set this in the system settings to half your physical memory divided by the number of simultaneous processes you expect to run.  If you have 8 GB of RAM and run 2 processes at a time, your Sort/Join memory should be set to 2GB. You might set it lower if you expect to be doing a lot of memory intensive stuff on the machine besides Alteryx   Set your Dedicated Sort/Join Memory Usage lower or higher on a per-module basis depending on the use of your computer, doing memory intensive non-sort work (i.e. large drive-times) then lower it, doing memory intensive sort-work then higher. *Please refer to this link for additional details on 32-but support for Designer
View full article
I recently set up an app that got me One Step Closer to Alteryx Nirvana. Check it out!
View full article
This little job shows how to connect Alteryx with CartodB.  It uses the  Download Tool   to pass to CartodB an SQL instruction for inserting  additional data within an existing table.
View full article
For those who haven't used CONTROL+[SCROLL on your Mouse]. You can use this with filters & formulas (maybe more?). The result is the difference between night & DAY!
View full article
Why play fair when you can stack the deck in your favor?
View full article
Steps that provide a better "Query Builder" in which you can build and copy SOQL for Salesforce Connectors.
View full article
I like to share my way of adding a new tool to the canvas without using the mouse. It might prove useful to one or another keyboard addicted Alteryx hacker ;)
View full article
You'll notice some of the menus have been rearranged in version 10+. So if you are looking to export a workflow package, you will find that under the  Options  menu,   Export Workflow .  By default, you will see all the assets that the workflow depends on to run properly, but sometimes you will want to include additional files to package with the workflow.
View full article
The attached macro will use the  Field Info Tool  and a  formula   to calculate the Excel column letter, e.g., for 755th field, the returned letters would be "ACA."
View full article
How do I use that tool? When it comes to tools, Alteryx has a full box! With over 200 tools and macros in the basic installation, you may wonder how you'll master them all. Don't worry - we have 'apps' for that!   Alteryx tools are designed to be focused and specific in their function - simple, yet powerful. Understanding what goes in and out while grasping what happens in-between makes the most sense in a workflow and that is why we have One-Tool Examples in the product.    One-Tool Examples   One-Tool Examples are sample workflows that demonstrate different configuration options for one tool at a time. You can access them a couple of different ways:   1 - From the Help Menu: Go to  Sample Workflows > Learn one tool at a time > Tool Category     2 - From the Tool Palette: Click on a tool and then click Open Example: The following list of tools, organized by tool category, have a One Tool Example in Alteryx version 11.5:   In/Out Preparation Join Parse Transform Reporting Data Investigation Developer Documentation Browse Directory Input Data Map Input Output Data Text Input                                     Auto Field Data Cleansing Filter Formula Generate Rows Imputation Multi-Field Binning Multi-Field Formula Multi-Row Formula Random % Sample Record ID Sample Select Select Records Sort Tile Unique               Append Find Replace Fuzzy Match Join Join Multiple Make Group Union                                   DateTime RegEx Text To Columns XML Parse                                         Count Records Cross Tab Running Total Summarize Transpose                                       Image Map Legend Splitter Builder Render Report Footer Report Header Report Map Report Text Table                                 Pearson Correlation Spearman Correlation                                             Dynamic Rename Dynamic Select Test                                           Comment Explorer Box                                                  
View full article