Alteryx Designer Desktop Knowledge Base

  Be sure to first understand that different people/organizations all have different definitions on what they mean by rounding.  Once you are clear on what you need, you can use Alteryx to round your numbers.   Example 1: Divided by the place you want to round to, use CEIL to round up, then multiply back by the place:   324 to 400 CEIL([Number] / 100) * 100   Example 2: Ceiling and floor just go to the nearest integer.  The ROUND(x, multiple) function will allow you to specify the level of precision on which to round, very similar to Excel.  But you can't specify the direction, so in your example, if your did ROUND(3425.123,1000) it would return 3000.   Example 3:  Please see the attached v10.0 workflow which is more dynamic. The text input identifies the mapping for the conversion joined with length of source input - then an expression is applied in the Formula Tool.         The final output.      More on rounding.

Connecting to Hadoop HDFS/Hive/Impala/Spark with Alteryx Designer.

This article was put together to resolve a common issue with cleansing your data as well as to show the use of tools and techniques that are not normally used for newer users. The goal of the article is to get newer users into these tools to open their creativity with the tool and hopefully take you to the next level!   In this use case, the data in the attached workflow is messy with capitalized strings all over the place. We want to format the data by removing some of the capitalization, but not all of it.   Note: If we wanted to make every first letter of the word capitalized we can use the Formula Tool and the TitleCase(String) function. This would make BEAR the WEIGHT - Bear The Weight. See the difference?   Example:   The tools that we will be using in this exercise is the Record ID, Text to Columns, RegEx, Formula, Tile, and Cross Tab Tools.     The exercise will show you the importance of using the Record ID Tool. The flexibility of the Text to Columns and RegEx Tools, the under-used Tile Tool, the creativity of the Formula Tool, and the not so scary Cross Tab tool when then data is configured properly.   We hope that these exercise and use cases open up your mind and the greatness of Alteryx!   See attached workflow and enjoy!

Filtering data is probably one of the simplest Alteryx functions, but it can become time consuming when building the expression, especially when filtering on a larger number of values. Wouldn't it be nice to be able to feed a list of values to Filter your data? I'll show you how you can do this with a simple batch macro.   Batch Macro: Setup the Filter tool within your Batch macro with a simple "In" statement, then use the Control Parameter to update the Value within the expression.     Connect the Control Parameter to the Filter tool, configure to update the Value within the expression as shown below, this will update that portion of the expression with the column the user defines in the macro.     Building the Expression: In a new workflow, Input the list of values you would like to use to Filter your data.     Connect your data to a Summarize tool, configure the Summarize tool to Concatenate your values into a single column using "," as the Separator and " as the Start and End.   Drag out a Formula tool in front of the Summarize tool, then lets add the  parenthesis as show below to complete the expression.     Filter:   Add your batch macro into the workflow by right clicking on the canvas and selecting Macro, you will need to browse to the location where you saved. Then connect the Formula tool to the "¿" of the Batch Macro. Input the data file you would like to filter and  connect to the remaining Input of the Batch Macro.     Finally, click on the Control Parameter tab and select the column to be used as the Control Parameter. We are not using the Group By function for this particular example, therefore there is no need to configure.     See attachment.

SPSS Output   Overview   When working with SPSS, values can have both a Text label and a numeric representation of the categories (equivalent of string factors in R). Columns can also have an encoded name ex. Q_1 and a longer descriptive name that maps Q_1 to the original question that was asked (closest thing in R is the data frame attribute on the column).   Alteryx reads .sav files and loads either the numeric representation or the textual representation of values based on the user’s selection. It also reads the variable labels into the Alteryx Field Description.  When writing .sav output, Alteryx will write either the text or the numeric values (depending on what was used in the workflow) as well as the SPSS variable labels which were displayed in the description field. However sometimes to maintain the integrity of the whole SPSS file, clients will want the value labels, value levels, and variable labels to appear in the output file. For these cases, using the Alteryx tools and a few lines of R code (while leveraging the thousands of R packages on CRAN) wrapped in a macro gives us the needed functionality. Attached is a macro which will write the data, variable & value labels back into SPSS.     Macro Process In this section, we will explain the R code logic that is specific to this macro. You can get an introduction to writing custom R code in Alteryx here.      Before we can do anything, we will need to pass the data to the tools inside the macro (more details on macros here). The raw numeric data should be connected to the D input of the macro. In addition, and because the data frames created in R don’t contain the Field Description data, we need to pass Field Description values to the M input (M for Metadata) of the macro. We’re using the Field Info Tool to extract the description into the rows and send it to the macro. With that done we can now look inside the macro.   Inside the Macro       Inside the macro, we are using the R Tool to contain the main functionality. We connect it to the Interface tools (Macro Inputs, File Browse, Action Tool) to get the data, metadata, and output file path from the user. Finally, we’re using the API tool to pass a message back to the user in the Alteryx Execution Messages.   The R Tool contains the code to properly format the input data and write it out to the .sav file. The majority of the work is already done for us in the ‘sjmisc' package from CRAN (R users know that most of the time they can find a package that does what they want). This package, among other features, implements reading and writing .sav files with both variable and value labels. We will first check if the package is not already installed and we’ll attempt to install it.   This workflow installs the additional sjmisc package. To avoid package version and dependency issues, it is possible to use Microsoft R Client as the base R. More details here.   if(!require(sjmisc)){ install.packages("sjmisc") require(sjmisc) }   If you would like to install the package separately you can use the R install packages App from the Alteryx Gallery.   filePath <- "c:\\temp\\outputRAlteryx.sav" inputData <- read.Alteryx("#1", mode="data.frame") ColumnLabels <- as.vector(read.Alteryx("#2", mode="data.frame")$Description)   Within the R code we also define a static ‘filepath ‘ pointing to where the output data should be written. The Action Tool that we previously mentioned will update this filepath to the one chosen by the user while at the same time correctly escaping the backslashes to work in R.   inputData <- read.Alteryx("#1", mode="data.frame") ColumnLabels <- as.vector(read.Alteryx("#2", mode="data.frame")$Description)   We then read the data that we pass to the macro from input ‘#1’ into an R data frame. In this case we are depending on R’s default behavior of transforming text to factors to get the Value encodings for all columns ex Male(1), Female(2). In addition, we read the metadata from input ‘#2’ into R. The sjmisc function, set_label, that applies the variable names to the data frame expects the variable names to be passed as an object of type vector. To make it work, we have to convert the Description column of the data frame we’re reading in into a vector with the as.vector() base R function. For more details about ‘sjmisc’, you can find the documentation here.   labeledData <- sjmisc::set_label(inputData,ColumnLabels) sjmisc::write_spss(labeledData,filePath)   Finally we label inputData with the labels we just created and we store the result in the labeledData dataframe and then write it to the user’s filepath using the sjmisc’s write_spss function.   MessageOut <- paste("file written to: ",filePath) names(MessageOut) <- "Output File Path" write.Alteryx(MessageOut, 1)   We also pass the filepath as a message to the R Tool output to be displayed to the user.     Edit: It was brought to our attention that the macro has an issue writing out text columns that are longer than 120 characters. Unfortunately this is a defect in the underlying R package. As a workaround for now, the macro was modified to trim all text fields to 120 characters. Please keep this in mind when writing out data.   Mandatory Note: This macro and sample code were developed by the authors as a proof of concept to show what's possible. This is not a production-ready macro and is not supported by Alteryx. Do ask questions on this thread - BUT use at your own risk!   WriteSPSSWithLabels_sjlabelled.yxzp has been updated from using the R package sjmisc because the set_label command has been deprecated from sjmisc and is now in sjlabelled.

Why play fair when you can stack the deck in your favor?

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.

This post includes THE 2017 Tips & Tricks booklet.

The Alteryx Gallery is full of interesting and useful Macros which provide 'out of the box' solutions to a lot of use cases! With well over a 1000 macros available which ones do you find most useful?

Recently a client reached out to us, saying that "all of a sudden" curly quotes were coming out of his keyboard, and Alteryx was rejecting them as unrecognizable. These are called SmartQuotes, and they are purely aesthetic. This can also happen when cutting/ pasting from other programs. Either way, Alteryx doesn't like them.   To change them back to the standard, useful straight quotes we all know and rely on, the first thing to try is CTRL SHIFT '   This will toggle between quote types.   An extra precaution is to make sure that your 'Regional Language' setting is English (United States); this is different than US English International. To check, in your control panel, go to Region and Language, Keyboards and Languages tab, and click on Change keyboards:   Make sure that your installed services is English (United States). If you do happen to require another input language, under the Advanced Key Settings, it will show you the key sequence to toggle between those keyboards.   This last step is likely unnecessary, but it's a good option to have in case CTRL SHIFT ' doesn't work (and I haven't yet seen it not work).   Now, back to writing formulas and queries with ease!    

With the release of 11.0, we see numerous changes to many tools in the Designer. The Linear Regression Tool gets a UI makeover and some cool new features are added that we will explore in this article. If you are new to performing regression analysis in Alteryx, I highly recommend checking out the Tool Mastery article which goes into everything there is to the old tool. Everything presented in that article remains valid as no features were removed. In this article, we will delve into the changes and new features.

Every new release of the Designer hosts the latest documentation at http://downloads.alteryx.com/documentation.html!

 

  ISSUE：   Error “ Error: Publish to Tableau Server (1): Tool #252: Tool #4: Tableau Server API Request (Publish file) Error Code 400011: Bad Request -- There was a problem publishing the file ‘ ’ “ when publish .hyper file of Excel with data source metainfo (.tds) file.   Repro Step:   Connect to an Excel file on Tableau Desktop Create an Extract with .hyper extension Then right click on the data source > add to Saved Data source, to create the metadata file (.tds) file Open Alteryx Designer, connect to the same Excel file using input tool Drag a publish to Tableau Server tool, type in all the relevant information, for Data source metainfo (optional), select the .tds file created in step 3 When run the workflow, this error would occur   Resolution:   Option 1 Do not create extract of the file, for text based file, do no need to create a local copy After created the .tds file (add to Saved Data source) , open the .tds file in a text editor like notepad ++ Modify on line 8, where filename = ‘ ’, change the path to UNC path + The original one looks something like: filename='H:/output.xlsx' + Change it to something similar to the following: filename='\\extendthereach.com\alteryx\CustomerSupport\output.xlsx'   Note: As a best practice, have the original Excel file in a share drive if it need to be used for other users in the organization or it will need to refresh on Tableau Server in the future. And make sure Tableau Server Run As User account has the sufficient permissions to access the share drive.   Option 2 -   If the Excel file has to be located on the end user’s machine, change the path in TDS file (line 8) to something like the following when the file in on local machine:    filename='\\?\C:\Users\kchen\Documents\Example named ranges.xlsx'   Note: If the Tableau Server Run As account does not have sufficient permission to access the share drive where the file is located, the following error would occur when connected to the Data source on Tableau Server:     “Unable to connect to the file "C:/Users/<Username>/Document/filename.xlsx". Check that the file exists and that you have sufficient access privileges for it. DataServiceFailure Unable to connect to the server "localhost". Check that the server is running and that you have access privileges to the requested database.”     See following Tableau KB for more information: https://kb.tableau.com/articles/issue/error-unable-to-connect-to-the-file-file-path-Check-that-the-file-exists-and-that-you-have-sufficient-access-privileges-for-it-refreshing-extract  

Symptoms   “Unhandled Exception occurred” error is thrown when you copy and paste text (Ctrl-V) using the R Tool.     Now, to witness it happening:     Diagnosis   Looking at the error log you will see error message below:   Default Log path - C:\ProgramData\Alteryx\ErrorLogs\AlteryxGUI         If you look at the log, you can see the error is directly related to the FIPS cryptographic algorithms. According to Wikipedia, FIPS stands for Federal Information Processing Standards and it is a “standard developed by the United States federal government for use in computer systems by non-military government agencies and government contractors”.   As of right now, “Unhandled Exception occurred” error will be thrown in the R Tool if FIPS compliance is turned on .    Solution   Our Development team is fully aware of the problem with the FIPS compliance and is planning to sort this out in the future releases. As for temporary solution, you can consider turning off the FIPS compliance, of course after checking and making sure your IT manager is okay with it, and this should resolve the “Unhandled Exception occurred” error.   Here’s how you can turn off FIPS compliance:   There are other ways to turn on/off FIPS compliance and you can find them here:   https://www.howtogeek.com/245859/why-you-shouldnt-enable-fips-compliant-encryption-on-windows/   Eddie Wong Alteryx CSE

  Our incredible user community came through once more with their most valuable Alteryx tips & tricks, these along with our own are now captured in our Tips & Tricks 2018 book.   Our sincere gratitude to our user community for their contributions and for attending our session at Inspire 2018 !   Enclosed is our 100 + pages of tips & tricks … enjoy !     Margarita, Henriette & Jessica Customer Support Engineering

“Ponder and deliberate before you make a move.”  - Sun Tzu, The Art of War

The cause and solution to the "DisableAllOutputForLicense" error with the Predictive Tools.

One trick I often use to conditionally update a field is the Boolean IIF function.  IIF is basically a shorthand version of a single condition test.

A guide to RgEx syntax in Alteryx!