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.
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!
Finding the future date after 30 from today is easy in Excel. Use the =TODAY function and add 30.
There's an Alteryx function - DateTimeAdd - that does the same thing:
What if we wanted to know the date 30 working/business days from today? Excel has a function called 'WORKDAY' to do that.
And it isn't limited to just today's date. You can use any date.
Finding a future date 'x' working days from today - or any day - in Alteryx is a little trickier. At least it was. Attached is an app you can use to pick a date and the number of working days in future.
From the snapshot above, you'll see the Calendar macro is used. There are two versions of this macro. One where the week begins on Sunday and the other where the week begins on Monday. In the attached app uses the macro where the week begins Sunday but the macro starting on Monday can easily replace it.
In this posting, we'll take a look at Excel functions that return today's date and current time. Then we'll see how to use Alteryx to do the same thing. We'll take this a step further and show how Alteryx can be used to return a large number of date-related information for any date using macros and apps.
To get today's date in Excel, you use the =TODAY() function.
And the =NOW() function will return today's date and time.
You can format date and time the way you'd like (eg. Nov-10 or November 10, 2016 instead of 11/10/2016).
Alteryx also has a couple of ways to get today's date and time. The first is macro available in the In/Out toolset called 'Data Time Now'. The tool's configuration provides many options for how you'd like to see the data, including date as well as date and time.
The other method is to use a tool (such as the Formula Tool) where an expression can be used with the function 'DateTimeNow()':
Use the same method if you want just the date or time.
There is similar function called 'DateTimeToday()' which will return the current data as of midnight (so the time comes back as 00:00:00).
What if you want information about a date other than today, however? I've written about calendar and date aggregation before and have made a calendar macro available for anyone to use. If you have a date in yyyy-mm-dd format, you join it to the Date field in the macro which returns the following fields:
Date: yyyy-mm-dd format; includes every day beginning 2000-01-01 through 2099-12-31.
Year: yyyy format.
Quarter: numeric representation of quarter (1, 2, 3 or 4 rather than Q1, Q2, Q3 or Q4).
Month: numeric representation of month; NO leading zeros.
MonthName: January, February, March, April, May, June, July, August, September, October, November and December; completely spelled out rather than an abbreviation.
WeekNumber: numeric representation of week; generally values range from 1–52, but occasionally a year will have a week 53; weeks 1 and 52 (or 53) may be partial weeks (i.e. less than seven days).
Day: corresponds to the calendar date with values from 1-31 (for months with 31 days); NO leading zeros.
DayName: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday; spelled out rather than abbreviations; Sunday is the beginning of the week.
DayYear: day of year; values range from 1-365 except for leap years which have a day 366.
DayQuarter: day of quarter; values range from 1-92.
DayWeek: numeric representation of week where 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday and 7=Saturday.
Week StartDate: date in yyyy-mm-dd format and data type = Date; week begins on Sunday with the possible exception of week 1.
Week EndDate: date in yyyy-mm-dd format and data type = Date; week ends on Saturday with the possible exception of week 52 (or 53).
Month StartDate: date in yyyy-mm-dd format and data type = Date.
Month EndDate: date in yyyy-mm-dd format and data type = Date.
Quarter StartDate: date in yyyy-mm-dd format and data type = Date.
Quarter EndDate: date in yyyy-mm-dd format and data type = Date.
I've taken this a step further and created an app with the calendar macro embedded in it which allows a user to select a date and the fields they want returned at run time.
I've made a couple of version of the Calendar macro; one where the week begins on a Sunday and the other where the week begins on Monday. In the attached app, the macro where the week begins on a Sunday is used but can be easily replaced by the one beginning on Monday.
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.
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.
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.
Question What is the difference between applications and workflows?
Answer An Alteryx Workflow (also known as a module) is the standard Alteryx file .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.
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 makes use of Interface tools to create user interfaces. Apps can be processed inside of Alteryx Designer (click the wand), in the Gallery, or stand alone.
For Analytic Apps, the Workflow - Configuration Type is set to Analytic App.
Be sure to check out our app building guidelines for information on how to build the best Alteryx Apps!
Attached is a v10.0 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:
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.
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.