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.
Now that it's live, don't forget to accept your certification badge on Credly today! Learn more here.
We are currently experiencing an issue with Email verification at this time and working towards a solution. Should you encounter this issue, please click on the "Send Verification Button" a second time and the request should go through. If the issue still persists for you, please email firstname.lastname@example.org for assistance.
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?
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.
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.
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.
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 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)
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"
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.
Any time you want to get a good point across, it’s best to show your data. Show your data off in style in your reports or presentations by adding formatting to otherwise bland data with the Table Tool! Found in the Reporting Tool Category, the Table Tool will make it easy flair to your raw data, and give it the pop it needs to really sink in.
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.
The Contingency Table tool is a part of the Data Investigation category in Alteryx Designer, which comes as a part of the predictive tools installation. Intuitively, you can use the Contingency Table tool to create a contingency table.
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.
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
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 😎 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.
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:
“Unhandled Exception occurred” error is thrown when you copy and paste text (Ctrl-V) using the R Tool.
Now, to witness it happening:
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 .
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:
As most of us can agree, predictive models can be extremely useful. Predictive models can help companies allocate their limited marketing budget on the most profitable group of customers, help non-profit organizations to find the most willing donors to donate to their cause, or even determine the probability a student will be admitted into a given school. A well-designed predictive model can help us make smart and cost-effective business decisions.
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