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.
So we’ve generated our list of Key Values (in Part 1) to be read by the Tree Interface tool and when we look at the Tree Interface it looks correct.
So how can we use these selected values in a workflow?
As a review, our original data consists of very pretend pet data.
Now we need to associate the Key Values to our data so that our data can be queried. This can easily be done by adding a couple steps to the workflow we create in Part 1 (updated workflow is attached).
By adding a Transpose tool, the table can be manipulated to get each possible Key Value for each record. Then a Select Tool to rename the Value field to Key. Those results are saved to a new file (Pet data with key values.yxdb) that will be used in the app.
When values are selected in a Tree, a list of the Key Values are returned with a line break between each value. In the above Tree Interface example (Figure 1) the Key Values 111 and 26 are returned as a string data type and would look like this:
Setting up the App
We now have all the parts we need to create the app: Pet Key values.yxdb (from Part 1) and Pet Data with Key Values.yxdb
The start of the app is a Tree Interface tool that points to the Pet Key Values.yxdb file, and an Input tool pointing to the Pet Data with Key Values.yxdb file.
We know the Tree Interface returns a list of Key Values, so we can then filter our pet data on the Key Values. A Filter tool is added with a template filter that will ultimately be updated by an Action tool, once we connect the Tree Interface to the Filter tool.
Now let’s connect the Tree tool to the Filter tool and configure the Action tool.
We are going to select the Action Type ‘Update Value with Formula,’ and update the full Expression. Now for the Formula: click on the ellipsis (…) to add this formula:
'[Key] in ("' + REGEX_Replace([#1], '\n', '","') + '")'
This expression will build an IN statement (Figure 5) based on the selected values from the Tree Interface. If the returned values from the Tree tool are, as in Figure 4:
The expression will build a string starting with: [Key] in (“
The REGEX expression looks for all line breaks (or newlines) in the connection 1 ([#1]) value, denoted by the \n, and replaces those occurrences with: “,”
And then finishes the expression with: “)
The final expression looks like this: [Key] in (“111”,”26”)
This will replace our template expression when the app is run.
To test your Action tool expression, use the Interface Designer’s Debug tool. Open the Interface Designer from the View menu in Alteryx. This Community article goes into more detail on how to use the Debug tool.
With that, the tree tool is complete. The Key Values from the selected Tree Interface items are used to select the desired records from the dataset for further processing.
Add a Browse tool to the True output of your Filter to view the results when the app is run. Be sure to configure your app to show the results of the Browse when the app completes. This setting can be found in the Interface Designer, under Properties.
As with many things in Alteryx, there is usually more than one way to accomplish a task. In the attached App example workflow, there is an additional method to take the results from the Tree tool and query the pet dataset.
All related workflows are attached to the post and saved in Alteryx 10.0 format.
Calgary is a list count data retrieval engine designed to perform analyses on large scale databases containing millions of records. One use case for a Calgary database in an App or Macro is to offer users certain predetermined criteria (e.g. geography) to select and then return the corresponding data to them. A back-end process can combine multiple data sources, format and pre-filter as needed, and load them into a Calgary database that can be queried quickly by the app or macro . This way, the bulk of the processing happens outside of the app or macro allowing data to be returned more quickly.
You may already know how to use the MIN() and MAX() functions to find the smallest and largest values in a list. But what if you needed the second smallest number or 3rd largest number in the list?
Excel has a function for this. Using the =SMALL function, you would specify the data range followed by 'x' smallest number you want to find. In the example below, we find the 2nd smallest value in a list:
Similarly, if you want the find the 'x' largest value in a list, you would use the =LARGE function. Here we use the =LARGE function to find the 3rd largest value in our list:
Let's look at how we do the same thing using Alteryx. We'll start with the same array of numbers using a Text Input Tool:
We want to find the 2nd smallest value in the list. We'll start by sorting the list in ascending order. Then we assign a record id to each row of data. Filter to select record id = 2 and use a Select Tool to drop the record id field (we don't need it in our final result) and that will leave us with our answer, '6'.
To find the 3rd largest value in the list, we simply change the sort order to descending and filter to select record id = 3:
Our result is '13', so everything checks out.
We replicated in Alteryx the Excel functions =SMALL and =LARGE. But let's add a couple of bells and whistles to our workflow and make it an app. This gives a user the ability to decide if they want to select the smallest or largest value from our list as well was what the value of 'x' will be. Begin by bringing a Drop Down Tool to the canvas.
Enter the text or question to be displayed ('Return smallest/largest value:). Under 'List Values' we'll choose 'Manually set values' and under 'Properties' enter:
Connect the Drop Down to the lightning bolt on top of the Sort Tool. Automatically an Action Tool will be inserted between the Drop Down and Sort Tools.
In the Action configuration widow, select @order - value = "Ascending".
Bring another Drop Down Tool to the canvas and enter the test or question to be displayed ('Enter Nth smallest/largest value:'):
Connect the Drop Down Tool to the lightning bolt on top of the Filter tool. An Action Tool will automatically be inserted between the Drop Down and Filter Tools:
Notice the expression in the Filter Tool is set up to send record id = 1 to the 'T' (true) output side of the tool. In the Action configuration window, select 'Expression - value = "[RecordID] = 1" and enter '1' under 'Replace a specific string:' located at the bottom of the configuration window.
Optional: Add an Output Data Tool to the end of the workflow so the results can be displayed. In this example, we will be exporting results to a temp html file:
So the complete workflow/app looks like this:
Let's run the app. Under the main menu and to the right of the run icon, click on the wand:
A window will pop up displaying the drop down menus you've setup with the Drop Down Tools. Let's find the 2nd smallest number in our column of numbers:
Click 'Finish' and a 'App Results' window pops up. Click on 'OK':
Our temp html files returns the value of 6 which is correct.
You now have an app other users can use to easily and quickly select the 'x' smallest or largest value in a list of numbers. To learn more about apps and interface tools in general, see here.
What is the difference between applications and workflows?
An Alteryx Workflow (module, for you old-school folks) is the standard Alteryx file format .yxmd. Workflows are what you build and save when you drag and drop tools into Alteryx Designer. Note: the Workflow - Configuration Type is set to Standard Workflow.
Workflow = yxmd
An Alteryx Analytic App is "a self-contained program that performs a specific function for the user" and is saved as a .yxwz. It is an easy to use GUI interface for users. Apps can be opened and used without opening Alteryx Designer, simplifying the look and feel for the user. Apps and workflows are both built with Alteryx Designer, but Apps make use of Interface tools to create user interfaces. Apps can be processed inside of Alteryx Designer (click the wizard wand), in the Gallery, or as a stand-alone.
click the wizard wand to watch the magic!
For Analytic Apps, the Workflow - Configuration Type is set to Analytic App.
yxwz derives from wizardry
Be sure to check out our app building guidelines for information on how to build the best Alteryx Apps!
Attached is an example of a very simple app. (.yxwz extension)
Other Alteryx extensions:
* Workflow: .yxmd
* Package: .yxzp Click Options - Export Workflow, to export a workflow or app and other files required to run (input files, custom macros, etc.)
* Macro: .yxmc
* Database file: .yxdb:
So you've built out your app and everything is working great, provided the user enters in all the information requested by your well thought out and organized user interface. How do you protect yourself from a flood of emails due to user error?
With the addition of a simple to configure tool, you can easily add checks and custom error messages if things are not entered correctly.
The Error Message Tool (Interface Toolset) is simple to configure and can provide the insurance you need to make sure that the information requested by your Analytic App is being entered correctly.
The Error Message tool connects to questions and allows you to build out a custom expression to ensure the data entered is what you're looking for. One of the most common uses is simply to make sure something was entered. To do this, connect your Error message tool to any question in your app and fill out the configuration as shown below:
When I run my app and leave the answer to that question blank, my error appears:
You can also use a single Error Message tool for multiple questions. Instead of building a separate message for each user required input, you can connect additional questions into a single Error and return an error if any of them are left unanswered:
You'll notice the questions are represented by [#1] and [#2] in the expression. This corresponds to the numbered connection line feeding into the Error Message tool. A best practice is to rename those lines so that you know which connection applies to each question. The process of renaming is simple. With any tool configuration window open, click on the connection line you wish to rename and enter the name you want to change it to. You'll see the change on the workflow canvas itself, as well as within the Error Message tool:
Now if a user leaves either one of the questions blank an error will appear stating that he/she must answer all questions.
A sample app demonstrating the setup is attached.
Sometimes clients have asked how they can re-input the same excel file that they have just outputted into the same workflow. Normally, their gut instinct would be to open up a new workflow and start fresh with the updated file. However, this can be a bit cumbersome especially if they want to do this multiple times or for those wanting to do some sort of logging process in an app. Luckily, there is a quick and easy trick for this.
Attached to this article is a workflow that I go through below...
Now the initial step to this might be different depending whether or not you already have a file that you want to bring in or not. If you are using just a regular Input tool, make sure you obtain the file path by selecting “Full Path” in the “Output File Name as Field” drop down. If you are writing in a text input make sure you have a field specifying the full path. This will be the same file path where we will output the data and bring it back in. The filepaths must be consistent throughout the workflow.
After your data blending and data preparation is complete and you are ready to output the file, place a “Block Until Done” tool at the end of your stream. Make sure your first output is connected to an “Output” tool with your specified settings with the proper file path. Next, place a “Dynamic Input” tool and make sure you are reading the field with the file path and change the action dropdown to “Change Entire File Path”. For the “Input Data Source Template “ box, I just reference the same file that I am going to write to.
And that is how you output a file then re-input it within the same workflow.
Did you know that instead of having to choose a save location in the Output Tool, you could leverage the directory that is used to save your temporary files?
This can be handy if you are running chained apps locally or have macros that have a file output process. To use temporary space instead of actually writing out a file to a specified location. All you have to do is add the following onto your file name: %temp%..\Output.yxdb. Once you run your workflow you can navigate to your temporary directory (which can be found in System Settings->Engine->General->Temporary Directory) to view your saved file.
The same path you used in the output tool can be used in an input tool to read in the file that is saved to the temporary directory.
Alteryx Analytic Applications (Apps) let us take a process, parameterize parts of it, and add an interface so that end users don't need to know all of the inner workings of the process in order to make it work for their specific scenario.
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!
In a workflow, not too far, far away...
Structured data has vanished. In its absence, the sinister Dirty Data Order has risen from the ashes of the Server and will not rest until Data Analytics have been destroyed.
With the support of the Alteryx Engineers, Solutions Engineer Tony Moses leads a brave RESISTANCE. He is desperate to find structured data and gain its help in restoring blending, joining and analytics to the galaxy.
Tony has sent his most daring Community Leader, Matt DeSimone, on a secret mission to Jakku, where an old ally has discovered a clue to the structured data whereabouts....
Welcome to the Star Wars universe!
Ever wanted to know the most important details of your favorite characters from Star Wars? Me too!
Our generous friends, Paul Hallett and team, have given us the Star Wars API - the world's first quantified and programmatically-accessible store of Star Wars data.
After hours of watching films and trawling through content online, Paul presents us all the People, Films, Species, Starships, Vehicles and Planets from Star Wars.
The data is formatted in JSON and has exposed it to us in a REST implementation that allows us to programmatically collect and measure the data.
Now, how was I able to retrieve this treasure of information via Alteryx? Easy! I've built a REST API connection using the Download Tool to pull information based on a user inputted query in an Alteryx Application (attached as v2018.1 Star Wars.yxwz).
Normally, once having retrieved JSON formatted data, structuring and parsing the data would be a nightmare! With Alteryx, this is just one tool away. The JSON Parse Tool allows you to identify the JSON field, in this case our download data field, and easily extract Name and Value columns. From there it's some simple formatting and using the reporting tools to present us a nice clean composers file (pcxml).
Man, if only the Rebels could process information as fast as Alteryx then they wouldn't have had to send poor R2 to find Obi Wan.
I'll be bringing you, the Alteryx Community, updates of the app with each new movie release!
I hope you enjoy the API and may the Force be with you!
Let's say you've got your eye a new car or home. Before visiting with a banker you'd like to run some numbers and see if payments fit within your budget. Excel has a couple of functions that can help you. To calculate your payment, you use the =PMT function. You can even take it a step further and calculate how much you'll pay in interest (=IPMT) and principle (=PPMT) for your first payment and each payment thereafter.
Payment applied towards principal (payment number 1 of 60):
Payment applied towards interest (payment number 1 of 60):
Just do that 59 more times, incrementing 'Loan Payment Number' by 1 until you reach 60 and you will create your very own amortization schedule. If this had been a home loan with a 30-year term (360 months), you would only need to update this spreadsheet 359 more times!
That's where an iterative macro can help and save you a lot of time. Attached is an app with an embedded iterative macro. It does the same thing the Excel spreadsheet above does but it loops through each payment period and calculates how much of your payment goes towards principal and interest and recalculates your new loan balance. When you run the app, you'll enter your loan amount, APR and the number of months in the term (length) of the loan.
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.