Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.
Ever have to output tables of differeing schemas to the same Excel workbook? Ever need to output to different tabs? This article covers your bases with the cunning use of Reporting tools! Also included are links to other helpful "outputting to Excel" Knowledgebase Articles.
View full article
Have you ever tried writing to multiple tabs within the same workflow and have received an error? This article is for you!
View full article
When importing a .xls file using the Input tool, Alteryx produces NULL values for specific cells, why does this happen?
View full article
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.
View full article
When creating reports within Alteryx, you can name Excel tabs by specifying a column to use in the Layout tool. This is done in a 3 step process. Step One:   Choose your grouping column; below, the Table tool is being used and [DMA_Name] is being used for the Group By field. This would also apply with the Charting and Map tools.         Step Two:   In the Layout tool, change the Layout Mode to Each Group Of Records. Next, choose the column you would like to Group By; for the example we will use [DMA_Name]. Next, change the Orientation to Vertical with Section Breaks. By doing this, the Section Name option will appear at the bottom of the tool configuration and allow you to choose the column you would like to use to name the Excel tabs.       Step Three:   Update the Render tool to create an Excel spreadsheet. In our example, each of the Excel tabs was named for the DMAs contained in the data.           Keep on reporting!
View full article
If you have a file that you want to output to separate Excel files you can first create the desired file path with the Formula tool and then utilize the Output tool to change the entire path.
View full article
If you have two or more files, different structure, and you would like to output each file into a separate tabs in an Excel spreadsheet. You could use the table tool to create snippets and the Layout tool to create sections breaks. Bring in your files using the Input tool and connect them to Table tools to create the snippets.  Finally, Join them by record position. The Layout Tool properties should look as follows.  Select Vertical with Section Breaks for the Orientation setting. The output will show each file in a separate tab:
View full article
How do I output to an Excel template file? It is possible to output your data to an existing Excel document that already has modified formats and column names. For example, the below Excel file has existing data in the first 4 rows. If you wanted to add addresses to this file while keeping the first 4 rows, the first step would be to highlight the area you want to write to. If you don’t know the exact length/width of your data, I would recommend going large: Once you have your desired area highlighted, right-click and choose the Define Name… option: A popup box will appear, enter in a name of your choosing and click OK: You also need to make sure that the sheet you are saving to doesn’t contain any spaces in the sheet name. Once verified, save the template and close out: Below is an example of the sample data that will be added to the above template: In Alteryx, use a Input tool to point to the data you would like to use to update the template file: In the Output, you will want to choose the template file, which will cause the below message to appear, choose yes to overwrite: When saving to Excel, the below window will popup, enter the name you used for the range you highlighted in the template file: After clicking OK, the Output configuration area will populate. Change the Output Options to Delete Data & Append: You can now run the module. Once the module is finished, you can open the updated template file, you should see your previously formatted rows/columns plus the new data you wanted to append: If you set a format to the range you named (color, text style, bold, etc), Excel will keep it so that the data you are writing to the file will appear with the specified format.
View full article
Suppose that your spreadsheet has multiple sheets with the same structure and you would like to read several sheets into your module at once. In this case, the preferred alternative is to use the Dynamic Input tool. 
View full article
Tips and tricks on how to output multiple sheets to an Excel file with the Output tool or with Reporting tools. 
View full article
In this article we’ll take a look at how to leverage a batch macro to read in multiple sheets from multiple Excel files using Designer version 10.0. The two Excel files I’ll be using in the example are called Fruits and Vegetables with sheets named Apples, Oranges, Broccoli and Spinach, respectively.   The first step will be to read in the list of sheet names from one of the Excel files using an Input tool. This feature is new to 10.0 and is a great addition when used in conjunction with the Dynamic Input tool.   We can then connect our Input tool from the previous step to a Dynamic Input tool. In our configuration, use the same Excel file as our Input Data Source Template. We can use the Sheet Names field from our Input tool as our List of Data Sources and our action is going to be to Change File/Table Name.   Now we’re ready to add our Interface tools to the canvas. Bring in a Control Parameter tool, 2 Action tools and a Macro Output. Connect the Control Parameter to the 2 Action tools, and then connect on to the Input and Dynamic Input. The Macro output will be connected to the output of the Dynamic Input.   In our Action tool configuration our action type will be Update Value. We want to update the File – value that is being passed on to the Input and Dynamic Input. We will, however, only need to update the portion of the string without the sheet name, which is why we’ll also select Replace a specific string: This will allow us to batch our process with multiple files.   Lastly, if our Excel files have different schemas, in the interface designer we can set the macro to Auto Configure by Name or Position so that our workflow does not error out. Note: sheets within the same file will have to be the same schema. We’re now ready to save the macro and put it to use!   Now, on a new canvas, we can place a Directory tool (If all your Excel files are in one directory), or, we can use a Text Input to manually enter each individual file path location. After the Directory or Text Input tool, insert your macro (right click on the canvas->insert->macro), then choose the full file path for your question in the macro configuration. Add a browse, hit run and look at the results!     We now are able to read in multiple Excel sheets from multiple Excel files!   The attached workflow package includes a workflow, batch macro and excel files created in Alteryx v.10.0   Tony Moses Client Service Representative  
View full article
how to reference other cells when creating formulas in excel
View full article
You may have a use case whereby you have a large dataset and you want to output it to separate excel files. However, in each of these excel files you would like to apply a template format.
View full article
how to dynamically rename headers on multiple files in a directory
View full article
Why am I getting the error "InvalidArgument=Value of '0' is not valid for 'itemIndex'. Parameter name: itemIndex" when trying to load an Excel file in and Input tool?
View full article
Question Can an excel template be updated if the incoming information is not in a block form?  Say I want to fill in a form from an existing list into something like this template.       Answer This is a question we get from time to time. There are a few options on how to do this, the below provided by
View full article
Can I read in an Excel file located ina zipped archive file from Amazon S3?
View full article
When using Excel spreadsheets in Alteryx, sometimes there may be difficulty in reading the data. Viewing the spreadsheets in XML format is an excellent way to check for encoding or formatting issues.
View full article
Unhandled Exception when clicking on the Browse Tool when using an R and Cross Tab in the workflow
View full article