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.
When publishing a workflow to Gallery or Scheduler (Designer + Desktop Automation) or when packaging a workflow for export, checking the boxes for what to include and what to exclude seems to work inconsistently as of Designer 2020.2. Here is a workaround to cover you until the issue is resolved in a future release.
This issue appears when an App writes to an Excel document. When the results are displayed and the Excel document opened, it will show as empty. As soon as the Excel document is closed, an Unhandled exception error will display, and the App cannot be closed.
Some Excel files, particularly those created by third-party programs, are encoded differently than Alteryx expects. In many cases, such files can be opened in Excel and then saved, resulting in files that Alteryx can open. Please refer to Designer Knowledge Base article entitled "How to check for encoding or formatting issues with Excel worksheets" for diagnosis tips.
Alteryx does a great job of simplifying our business processes, eliminating the need to maintain, document, and use Excel Macros. However, for that one workbook with 100’s (or even 1000’s) of lines of VBA code + months of development behind it, we have a simple way to integrate that Excel Macro within your workflow. This can greatly ease the transition from Excel to Alteryx and save you rework or buy you time to convert the process.
What we will use:
Alteryx’s Run Command tool
VB Script to call our Macro
An example can be found attached at the end of this post. Simply extract in Alteryx and Open.
To implement is very simple – place the Run Command tool where you need the Excel Macro to run in your workflow. This example shows the most basic workflow.
The command we will be executing is CScript, which can run Visual Basic Scripts. We will need to provide it a script; a simple one is shown below.
workbook_path = ".\VBA_Example.xlsm"
' Place your workbook file here
macro_name = "Macro1"
' Place your macro name here
set file_system = CreateObject("Scripting.FileSystemObject")
full_workbook_path = file_system.GetAbsolutePathName(workbook_path)
' File address housekeeping
Set ExcelProgram = CreateObject("Excel.Application")
' Tell the script what Excel is
' Open your workbook
ExcelProgram.Application.Visible = False
' Open it in the background
' Run your Macro - this tells the Excel running in the background to find this workbook and macro.
ExcelProgram.Application.displayalerts = False
' Do not show prompts since we want this to be automated - Could switch to True to get prompts
' Do not forget to save your work
' Close the workbook
This script will call “Macro1” from “VBA_Example.xlsm” – the macro, which is quite simple, puts the current time in the workbook.
Here is how the Run Command needs to be configured to call this script:
Write Source – 2 recommendations here:
Save off your data (attached example).
Insert the data you are going to use in the macro, into a tab on the workbook.
Read Results – again 2 recommendations:
Read the resulting worksheet the Excel Macro modifies (attached example).
Read the data you originally saved coming into the Run Command.
Command – CScript ; this Windows program executes VBScript files as if they were in the command line
Command Arguments – the filepath to your VBScript that calls the Excel Macro.
Working Directory – this could be left blank; however, it should be set to where the VBScript file is. The example uses the workflow directory since that is where it is.
Run Silent / Run Minimized – this should be set to match the True/False value in the VBScript below:
ExcelProgram.Application.displayalerts = False
' Do not show prompts since we want this to be automated - Could switch to True to get prompts
That’s all there is to it.
Excel has a new function called XLOOKUP. It's still in beta so you may not have access to it yet. The help page includes several examples of how to use it. The attached workflow contains snippets that show how to accomplish these tasks in Designer. Three examples are explained below.
Look up employee information by employee ID number
The basic XLOOKUP functionality is included with the Filter tool...
Find next largest item
With the match_mode argument set to 1, XLOOKUP will look for an exact match, but if it can't find one will return the next larger item. To do this in Designer, we'll again start with the filter tool to narrow down the tax rates to those above the income threshold, then we'll sort by max income and choose the first record.
Sum values between range
In this example we're choosing to sum the total prices of the products between Grape and Banana inclusive. The Excel function is a bit complicated here, with a sum and two xlookup functions. If there were a numeric order or record id column we could do this pretty easily with one or two Filter tools, but as is with the arbitrarily ordered product categories we'll use a Multi-Row Formula tool to decide which rows to include in our sum calculation.
There are other ways to accomplish XLOOKUP functionality in Designer. Feel free to suggest your favorite in the comments below.
Some time ago, there was a nice writeup: The Ultimate Alteryx Holiday gift of 2015: Read ALL Excel Macro: Part 2. This amazing macro allowed me to read any excel file, regardless of the number of tabs.
Until I start working with users that use diacritics on the sheet names.
If we try to use the mentioned macro, you will receive an error like this: ‘not a valid name’
I decided to approach this as a macro (2 levels) and use the Directory functionality to read all possible xls files within a folder.
Level 1: Read xls Tabs
This macro will read all the tabs for a single xls file. I used an R tool that includes the library readxl.
This library allows us to read xls files. I used the excel_sheets function to extract the sheet name and compile the sheet name with the name file path. You will receive a column per tab that the xls file has. I cleaned these two values and passed them as Path and Tab.
This data is sent to the Read xls file macro.
Level 2: Read xls Files
This macro gives structure to the full path (Path + Tab) using the structure needed in xls files. It uses the Dynamic Input tool to dynamically choose the data and output its content.
Update the XXXX for your corresponding paths
Don’t forget to install the R library readxl under your %Program Files% path g. C:\Program Files\Alteryx\R-3.5.3\library
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.
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.
You probably already know that you can output results to multiple sheets of an Excel file. If not, you should check out our resource that explains how to do that very thing. But what if you run that workflow every day, and you want to keep the outputs from days past?
To find the full path and filename of a saved file in Excel, you use the =CELL function.
In Alteryx, you use a Field Info Tool to get this information:
The Field Info Tool allows you to see in tabular form the name of fields in a file as well as the field order, field type, and field size.
Name: field names within the file
Type: type of data field
Size: length of a data field
Scale: with respect to fixed decimal data types, scale refers to the digits of precision
Source: contains the full path and filename
Description: may or may not contain information; you can add a description via the Select tool
We're only interested in the Source field and this information will be the same for each field.
Using a Sample Tool, we select just the first record:
Notice the data in Source begins with 'File:'. We don't want that in the final output so we'll use a SUBSTRING function in the expression of a Formula tool to clean it up. Complete the workflow with a Select tool so we only get the Source field:
I'll mention here you can use the Directory Tool to find the full path and filenames in a directory.
Select the directory you want to search. File Specification has wildcard characters so you can limit your search to files containing specific character patterns or file types. In the example below, let's set up the File Specification to only return files with the '.xlsx' file extension:
We're only interested in the field 'FullPath' (first column) so we'll use a Select tool to drop the remaining fields.
While the Directory tool returns multiple filenames, it will not contain a worksheet name if the file is an Excel file. To get that information, you'll need to use the Field Info Tool as we did above.