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.
Is there a workaround for not being able to use the Folder Browse Tool in the Gallery? Though it may not be as clean as being able to use the Folder Browse Tool, the simple workaround for this is to use the Text Box Interface Tool instead. This will allow the user to copy a directory path from Windows Explorer and paste it into the Text Box. In the workflow, all you need to do is connect the Text Box Tool to an Output Data Tool and have the Action Tool update the path portion of the Output Data Tool. You can even enter in a default path in the Default Text section of the Text Box if there is a path that is most commonly used.
The 'How to Guide' to Cognitive Services Text Analytics Macro
As Microsoft continues to grow it's Machine Learning capabilities, Alteryx is following suit and has built a new connector taking advantage of Microsoft's Cognitive Text Analytics API.
The new Cognitive Services Text Analytics Macro will replace the AzureML Text Analytics Macro, keeping sentiment analysis and key phrase extraction, while also adding Language and Topic detection.
To use this macro you will need to create a Microsoft Account, as well as signing up to the Microsoft Azure Account.
Sign in or create a Microsoft Azure Account and this should take you to the Azure portal. You will need to click on 'Create a resource' and search for 'Text Analytics'.
You can then click 'create' in the bottom right corner.
You will need to set up a subscriptions if you haven't already. Once you set up an account, you can return to this stage and continue.
Once you have set up a subscription you will be able to create an application by filling out the following window. Hot 'Create once you are finished and this prompt azure to deploy this application (This may take a minute - you should see it in the top right of the web browser).
You can then click on the notification tab and Pin to Dashboard then go to resource.
Once you have done this you should see the screen below: (If you don't please refresh or sign in & out of cognitive services until you see the information you added above within the information ribbon below).
Click on keys, then copy and Paste them into the Cognitive Services Macro and select your field and type of text analysis.
Error: Invalid subscription key
This error tends to occur because the account hasn't fully been processed by Microsoft.
Solution: Please log in & out of Azure, verify the information looks correct and then copy and paste the key into Alteryx.
Upon creating a BINGO game, I came across a technique that I thought could be useful in "real world" scenarios for users who are attempting to iterate a process and then replenishing the data after a certain amount of time.
A must-have for any app or macro, the Error Message Tool displays a prompt to the user based on input from Interface Tools . Using any expression that evaluates to true, along with any number of user inputs from question anchor connected Interface Tools, the Error Message Tool can layer even the most involved applications with failsafes that ease a user’s experience through more robust interactions.
For any macro or analytic app – one of the inevitable questions that you may encounter is “how do I configure this to do what I need?” For example, if you build a macro that checks if two fields are equal, but sometimes you want to ignore the case such that “A” equals “a,” and sometimes you want an exact match. This is where the Interface Tool Category comes to the rescue, with a super-tool called Check Box!
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.
Jordan Barker & Fadi Bassil
One of the best things about Alteryx is the ability to read in multiple files very easily and automatically combine them into a single dataset. This becomes a bit trickier when dealing with files that have different schemas or Excel files with multiple tabs. Adding both multiple excel files with multiple tabs, and having the schema change within each tab takes it to another level.
The key component of any batch macro , the Control Parameter Tool is the gear that keeps things moving. Using the input , the control parameter accepts a field of values that will be used within the batch macro to reconfigure and rerun the macro for each of the standard input records - unless using the GroupBy feature that matches certain control parameters to buckets of records to be batched through the macro together. Adding this interface tool to any macro will upgrade it to a batch macro and will give you the ability to loop through macro configurations for added customizability. While one of the more sophisticated solutions you can build into your workflows, there are few problems you can’t solve with a batch macro:
There’s a lot going on in the world of analytics. Endless data stores and insight are at the other end of an internet connection and, as analysts, we’re always in on the action. Being in the thick of the fray with data whizzing by at lightning speeds, being equipped with the right tools is a must. Like you, Alteryx also likes to live dangerously, and we’re always ready for action.
Fact: workflows are the best. Look it up. They’re all about getting things done and, with hundreds of tools and the ability to integrate external processes , there’s no shortage of things you can get done. We know that there are some areas of analytics that require a little extra firepower, however, and that’s why you can leverage your workflows in apps and macros for added functionality.
Have you ever used the Allocate tools and received back some strange looking variable names? You're not alone! The Allocate Rename Fields Macro will allow you to rename your fields into readable variables.
The macro can be downloaded here. Note: This will navigate you to the Alteryx Gallery. Select "Download & Install the Allocate Rename Fields Macro" and follow the prompts to install.
USING THE TOOL
The Allocate tools allow users to enrich their workflows with third party data provided from Experian and the US Census. This data contains demographic and household information by geography. Allocate tools can be found under the “Demographic Analysis” tab in the Alteryx toolbar; they include the Allocate Input, Allocate Append, Allocate Report, and Allocate Metainfo.
Allocate Input and Allocate Append tools allow users to select variables to display by geography. Once configured, the fields returned look something like this:
Add the Allocate Rename macro after the Allocate Input/Append. In the Configuration window, select the Dataset that you are pulling from. Press Run for the magic!
Voila! Your field names are now human-readable.
What if my company blocks access to downloading new tools/macros from the Gallery?
In the case that you cannot download this macro, you can use Alteryx to dynamically rename the field names. See is it possible to get the variable name I see in the Allocate tool?
Craig Bloodworth from The Information Lab posted the below in the Community Forum discussing a macro he has built to make pulling information from MongoDB easier!
Building on this KB article I've created a macro which will do all the work for you and allow you to send the data into your favourite data storage device.
You can download just the macro from the gallery.
If you use Tableau and want to take advantage of a pre-built workbook take a look at this workflow.
There's also a blog post on The Information Lab website.
Here's some screenshots of what you can extract:
Question Can you wait X seconds between processing each row in Alteryx?
Yes! Thanks to Invisio for creating an Inviso Macro Pack and posting on their blog here.
The "Wait a Second" macro lets you wait X number of seconds before processing each row in the dataset.
One application is if you are contacting an API with multiple requests. The WaitAsecond macro may help to pause the API long enough to process multiple rows without issue.
It can also be used to scrape sites without putting heavy loads on their server. An Invisio sample of scraping the Alteryx community (See Insights to the Alteryx Community)
As you can see, the part of the flow that runs through the WaitASecond tool gets a NOW timestamps which are 5 seconds a part, whereas the bottom stream, not running through the WaitASecond tool, all gets the same timestamp.
There are essentially two macros:
The first one assigns a unique id to each record and then uses that ID for the batch macro.
The batch macro has a “Command tool” that runs a ping that waits x seconds before timeout (18.104.22.168 if that exist in your network it won’t work).
The macro can be downloaded here (InvisoMacros.zip).
Question Have you ever wanted your own help page for your custom macros or applications?
Answer If you create your own macros or applications and send them to other who aren’t as familiar with your project, or if you just need a refresher from time to time, you may try and access the help menu only to be greeted by the general Alteryx macros/apps pages:
Macro Workflows Page
Analytics App Workflows Page
You can actually create your own help pages/files that can be accessed how you would normally access the Alteryx Help Menu for any "out of the box" tool that comes with the Designer.
Using your favorite text editor (Microsoft Word, for example), you can create your help file with any instructions or graphics that you feel would be helpful to the end users who may need to access a help file. Once you are done, you can save this in any file format that your (or your users') machine is able to open, as well as any location those users would be able to access (a network drive for example).
In your application or macro’s Interface Designer Properties, there is an option to add the path of a file or hyperlink to your newly created help file.
For an example I created the following help file as a .docx, .pdf, and .htm file type. Each other these files open in their respective default programs.