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.
Can Alteryx Write Custom Messages to the Output Log?
Yes! Take a look at the Message Tool. This tool allows you to create custom messaging within Alteryx based on conditions you can specify.
First decide when you want the message to be sent to the output log:
Before First Record
Before Rows Where Expression is True (specify your condition in the expression box)
AFter Last Record
After All Downstream Tools Have Completed
Then you can specify the Message Type choosing from:
Field Conversion Error
Error - Stop Processing Records
Once you select your Message Type, you can choose your Priority from Normal, Medium, or High.
Finally you can specify what the message is actually going to say. Whatever you put in the Message Expression box will show up in the Output Log of the Results window. You can also build out your message using fields if you need to indicate what piece of the data is causing an issue or needs to be called out.
Pull together the options to customize the messages you're seeing so you can follow the progress of your workflows and data.
One of the great features of the output tool is the option to take the file or table name (or part of it) from a field. It allows you to append a suffix, prepend a prefix, change the entire file name, or the entire file path. It also gives you the option whether to keep the field on output.
To go along with our example on how to download a file from FTP, we’ve assembled steps in the attached workflow (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:
One of the great things about Alteryx is the ability to have multiple geography types (points, lines, regions) all contained within the same record.
However, when exporting map layers to MapInfo Tab file format, a decision has to be made of which spatial field to keep. What if you want all of them?!
Not to worry! The Transpose tool will save the day!
To configure the Transpose tool all fields will need to be selected under the Key Fields except for the spatial fields, they will be selected under the Data Fields.
Just note that there are now 2 fields for the radius. RadiusSize will correspond to the first trade area (row 2) and RadiusSize2 will correspond to the second trade area (row 3).
Now, more importantly all the spatial objects exist in one field (the Value field) and can now easily be exported to MapInfo.
Note: this methodology will also work for ESRI shapefiles, however, all the spatial objects must be of the same type. All points, all polygons...
(An example workflow is attached. This workflow was created in Alteryx 10.0)
When you’re frequently writing and rewriting data to Excel spreadsheets that you use for Excel graphs and charts, it can quickly become a hassle to make and remake your reporting objects to keep them up-to-date so you’re visualizing the most recent data. A best practice to keep the hassle out of the process exists, though! If you keep your plots isolated to their own spreadsheet, referencing cell values in another sheet used to capture your data, you can simply overwrite the source data sheet and your plots will update automatically upon launching Excel. In the example below (attached in the v10.6 workflow Dynamically Update Reporting from Excel Spreadsheets.yxzp) we’ve included the workaround to make your Excel outputs seamless.
Did you know that you can connect to a Dropbox or Box.com account from Alteryx on your desktop?
As long as your install the desktop client for each service you will be able to read and write to each of the accounts.
To download the desktop client for box.com click on your name/username, select “Get Box Sync”, and then click the “Box Sync” download button:
To download the desktop client for dropbox click on your name/username, “install”, and then click the “Free Download” button to start your download:
You will have to run through the install wizards for each program and one of the processes during install is to choose an installation path. Make sure that you choose a path that you have permissions to both read and write files as this is key to connecting to the services with Alteryx. Once the install has completed and both (or one) of the services are running you should see them active in your “Favorites” section of a windows explorer window as well as on the task bar:
If you were to access either of those services in the windows explorer window you will be able to see the install path for each. That install path is what you would use in your input or output tool in order to read and/or write to either Dropbox or Box.com.
In both Dropbox and Box.com I have uploaded a file “AddressData.yxdb” via their respective web interfaces as an example:
An input tool can now be used to path to each of the directories on your machine where dropbox and box.com were installed and you will be able to see and read the files into Alteryx:
You can then do any processing you need to on your file and then write back to each service. Box.com will let you overwrite the file you are reading in as long as you have a Block Until Done tool somewhere between input and output; the file is locked while it is being read. The nice thing about box.com is that they provide version information when you do overwrite an existing file. This version information allows you to download or replace the current version with previous versions:
Dropbox will allow you to write back to its service, however you cannot overwrite an existing file. Dropbox puts a lock on files being accessed so in order to overwrite or “update” an existing file, you will have to rename the file in the output tool from “AddressData.yxdb” to “AddressData2.yxdb” for example.
Is it possible to change a Qlikview Dashboard to Tableau using Alteryx?
Alteryx is able to read Qlikview data files (*.qvx) via the Input Data Tool. You can then use the Output Data Tool to create tableau data files (*.tde). While it is possible to move the data visualizations, it cannot be converted. Try simply reading in the Qlikview file and outputting to a Tableau file; you will need Tableau desktop to build the new Tableau dashboard.
Input data - browse to .qvx file:
Output data - browse to new .tde file:
Sample .tde extract:
Once you have started a workflow within Alteryx it's hard to think about leaving! However, if you feel like that time has come, Alteryx makes that transition easier than Micheal Phelps winning Gold in the Olympics. The Output Data Tool is used to write the results of your workflow to any supported database or file formats ; Alteryx also offers the opportunity to output directly to Tableau Server , and Power BI . Using the Output Data Tool, you can:
API connections give access to many web-based applications, database systems, or programs by exposing objects or actions to a developer in an abstracted format that can easily be integrated into another program.
Can an excel template be updated if the incoming information is not in a block form? Say I want to fill in a form from an existing list into something like this template.
This is a question we get from time to time. There are a few options on how to do this, the below provided by
Believe it or not, data can be beautiful. Take your black and white data points and add some color to them in visuals with the suite of tools found in the Reporting Category https://help.alteryx.com/current/index.htm#Getting_Started/AllTools.htm#Report_Presentation_Tools ! If you’re looking to create reports, presentations, images, or simply output data with a bang, you can use the Render Tool https://help.alteryx.com/current/PortfolioComposerRender.htm paired with other Reporting Tools to create HTML files (*.html), Composer files (*.pcxml), PDF documents (*.pdf), RTF documents (*.rtf), Word documents (*.docx), Excel documents (*.xlsx), MHTML files (*.mht), Power Point presentations (*.pptx), PNG images (*.html), and even Zip files (*.zip) – packed with formatting and visual aesthetic that’ll make any data-geek’s mouth water.
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.