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.
First added to Alteryx Designer in version 10.6, the Optimization Tool is a member of the Prescriptive Tools (included with the Predictive Tools installation) and allows you to solve optimization problems. Mathematical Optimization is the selection of the best possible option(s), given a set of alternatives and a selection criterion. In this Tool Mastery, we will review the inputs, configuration, and outputs of the Optimization Tool.
If you are developing with Alteryx's In-Database tools you may want to share the workflows with users who do not have as much database experience. Below is an example workflow to create a macro that will allow your users to select which fields are brought out of the in database tools without interacting with the in database tools directly.
The premise of the process is that you can stream out a single record of your table, then leverage a List Box tool in select mode to de-select the fields your user does not want to pull. From there, you can use Field Summary and Summarize to create a select statement that will be used to update a Dynamic Input In-DB tool.
To run in your environment, update the Connect In-DB tool to a connection that exists on your machine and start with a select all query.
Imagine this – you’re on vacation. You’re on a sandy beach where the sun has been relentless all day. It’s hot and you need something to cool you off. Ice cream would be perfect! The Find Nearest tool can help!
The Sample Tool allows you selectively pass patterns, block excerpts, or samples of your records (or groups of records) in your dataset: the first N, last N, skipping the first N, 1 of every N, random 1 in N chance for each record to pass, and first N%. Using these options can come in the clutch pretty often in data preparation – that’s why you’ll find it in our Favorites Category, and for good reason. While a great tool to sample your data sets, you can also use it for:
You’re creating an app that involves dates. You want the user to be able to dynamically select the dates being used in the app, though. The tools you already know may not work. A Text Box would be too messy and allow lots of room for error. Pre-defined Drop Downs and List Boxes aren’t dynamic enough. Ah ha! What about that tool that looks like a calendar? The Date tool in the Interface category provides the perfect solution!
Trying to convert all of your old, mundane Excel workbooks into Alteryx workflows? The Running Total Tool could be the key to your success! You know, it’s that tool in the Transform category with the little running man picture on it.
A close relative of the Layout Tool , the Visual Layout Tool is the newest, and coolest, Reporting Tool on the block. Sporting all the badassery of the original Layout Tool in its ability to format and arrange reporting objects, the Visual Layout Tool differentiates in that it provides an intuitive, visual, interface that allows for easy drag-and-drop organization of multiple reporting object inputs. Basically, it’s the reporting tool category equivalent of upgrading from Paint to Photoshop.
If you are building a predictive model, inevitably you will want to analyze the effect that your independent variables have on your dependent variable. This article is meant to shed some light on the Alteryx-specific options for this type of analysis!
The Dynamic Replace Tool is an under-utilized tool in the Developer Toolset that is very powerful. It allows for dynamic formulas or conditions to be used in your workflow. It was first introduced in Alteryx 6.1 . It’s one of the few tools that is currently multi-threaded which makes is fast.
Welcome to the closing chapter of our voyage through the Pre-Predictive series! This has been a four-part journey introducing you to the thrilling world of data investigation. This section covers the plotting tools included in the Data Investigation Toolbox.
A common task that analysts can run into (and a good practice when analyzing data) is to determine if the means of 2 sampled groups are significantly different. When this inquest arises, the Test of Means tool is right for you! To demonstrate how to configure this tool and how to interpret the results, a workflow has been attached. The attached workflow (v. 11.7 ) compares the amount of money that customers spent across different regions in the US. The Dollars_Spent field identifies the amount of money an individual spent and the Region field identifies the region that the individual resides in (NORTH, SOUTH, EAST, WEST).
A few months ago, I found the free space on my hard drive shrinking significantly on a daily basis, but for the life of me I couldn't figure out the cause. As it turned out, I was suffering from a Windows issue that I was not alone in dealing with. I was able to get the issue resolved by our IT department, but not before I spent a fair amount of time searching through my directories, compressing and deleting files, running Disk Cleanup, etc., none of which was particularly helpful. So, I decided to look to Alteryx for an answer.
I created a simple workflow (attached and created in 11.0) which uses the Directory tool to read the attributes of all of the files on my computer. Then, I grouped the files by directory and summarized the file size. After a sort, I instantly found the culprit. There were hundreds of CAB files (a Windows archive file format) that were 100 MBs each which were being created erroneously by Windows. I was able to share this with my IT associate which helped him greatly in diagnosing and subsequently fixing the issue.
Most of you are unlikely to be experiencing the same issue (but if you are, you're welcome ). However, this workflow may come in handy for other reasons. If anything, just to help you manage your files and disk space by pointing out which files are hogging up all of your hard drive space!
Thanks for reading!
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.
How do I know if I need to use a Join or a Union tool?
This is a fairly common question. You have two data sets and you need to combine them into one larger data set, but how do you go about doing that? Alteryx has two tools that make this very easy depending on what you're trying to accomplish; the Join Tool and the Union Tool.
So what is the difference anyway?
The Join tool will make your file wider, meaning it adds fields to your data, or combines your inputs horizontally. You want to use this tool if you're looking to add information for existing records. For instance maybe you have a list of customers with the store ID that they shop at, and then have a separate list of the Store locations. If you want to add the Store information to the Customer information, you'd need to do a Join based on the Store ID field. This will match record by record the Stores with their Customers so your Customer file now has the Store information on it. Note that this can result in duplicate records depending on how your data sets are set up so be prepared to check your data.
The Union tool makes your file longer, meaning it adds records to your data, or combines your inputs vertically. You want to use this tool if you're looking to stack two files on top of each other. For instance, maybe you have a list of customers from each region of your sales organization and want a single master customer file. All your region files have the exact same fields. The Union tool will align your data sets based on the Field names (either automatically or you can do it manually if there are slight variations) and you'll end up with one master file with all the same fields.
Take a look at the attached sample built in v11.3 that demonstrates the difference!
Amazon offers many different services that can be accessed from the AWS Command Line Interface (aka the AWS CLI). Alteryx workflows can take advantage of these services using the AWS CLI from the Run Command tool.
Some of the common uses of the Amazon CLI within Alteryx include:
Using S3 with IAM roles
Uploading/Downloading file types not currently supported by the S3 Upload / Download tools
Interacting with Athena
As an example of using the AWS CLI, let’s build a process that can take a local file and copy to S3, using an AWS IAM profile. Here are the steps involved:
Review the documentation for the service. In this example, we're using the "S3" service, but Amazon has many different AWS services.
Install the AWS CLI on any machines that will need to execute the AWS functions
From Windows Command Line, create a working command
(Optional step to make calling the CLI easier in the future) – Add reference to the AWS CLI executable to Window Environment variables via My Computer > Right Click > Properties > Advanced System Settings > Environment Variables > System Variables > Select “Path” > Edit > (Add the path to the directory of your installed “aws.exe” file, separated by a semicolon. Do not delete/modify any pre-existing values in the Path variable.) > OK > OK
Per the AWS documentation, create a local profile using your IAM role. The local profile references your credentials so that you don't have to pass the credentials in every command.
Test the following command in your Windows command prompt, making sure to use a valid local file to copy, and a valid destination location on your S3, and a valid profile name:
s3 cp "C:\temp\mylocalfile.csv" "s3://mylocation/mydestinationfilename.csv" --profile MyIAMUser
Once you've validated your command in the command prompt, transfer it to an Alteryx workflow
In a new workflow, pull in a “Run Command” tool from the “Developer” category
Configure the Run Command tool using the working command:
Test the above
As an optional step, we can wrap this functionality into a macro to make it easy to update, and to support batch uploads
Add a Control Parameter for “Local File Path” and configure its action to update the specific string value “C:\temp\myfile.csv”
Add a Control Parameter for “Destination File Path” and configure its action to update the specific string value “s3://mybucket/myfiletest1.csv”
Add a Text Box and configure its action to update the command value “aws” in case the user hasn’t placed the CLI exe in their PATH variables
The example above is attached in the 11.3 workflow, SaveToS3_usage.yxzp.