How to import a .TWB in Alteryx

This initial blog post and tip forms the ground work for a wider project that will show how we can use Alteryx to allow a user to select specifically what content they would like in their final workbook. What is values are contained within the filters? what values exist within a parameter? what worksheets exist on a dashboard? what dimensions exist in a table? the list is pretty endless.

I’m going to call this series ‘Controlling Tableau from within Alteryx.’

But 1st things 1st, we need to be able to import a workbook directly into Alteryx, not as easy as you might think given that a .twb is not a support file type (although it’s definitely not hard!).

Ofcourse the process still starts with an input tool, so drag one onto your canvas. Then within the input configuration panel, navigate to your .twb file, a note here is that we need to be sure we change the ‘Files of type’ option to read ‘All Files’, we then select ‘Open’.

Now Alteryx is going to ask us to resolve our file type. Within this pane we are going to select ‘Read it as a delimited text file’, we are going to select the ‘Other’ delimiter type and use ‘\0’, we are also going to uncheck the ‘First Row Contains Field Names’ option.

The image below shows an example configuration.


Once you press ‘OK’ you will now see in the preview panel you have one field, aptly named [Field 1]. This one column contains every row of XML (.twb is essentially an XML file) that lies beneath the Tableau work you have created.

Be sure to change the Field Length variable which is automatically set to 254 characters, even with a blank workbook you will loose some characters from your XML strings and thus corrupt the workbook. I as a standard set mine to 2000 characters, though this may affect performance. Whatever you set this too, be sure to check for conversion errors that suggest records in [Field_1] have been truncated.




You now have all sorts of opportunity in your hands to amend your workbook from outside of Tableau and inside of Alteryx.

A word of caution though, any changes you make will definitely not be supported by Tableau.

Be sure to look out for future posts!



Using the ‘events’ tab in Alteryx to include documentation with a workflow/macro/application package

Recently I have been working on a project which involved running a series of .bat files at particular points in an Alteryx workflow. To do this we were using two Run Command tools, one at the beginning and one at the end of the workflow. This process was to unlock and lock a database so that it could be edited within the workflow.

Randomly, after I bit of exploring around the Alteryx user interface, I came across the ‘Events’ tab available in the workflow configuration pane.


What the ‘Events’ pane allows us to do is three things.

  1. Run Commands, either ‘Before Run’, ‘After Run’, ‘After Run with Errors’ and ‘After Run without Errors’
  2. Send emails, either ‘Before Run’, ‘After Run’, ‘After Run with Errors’ and ‘After Run without Errors’
  3. Add assets

The ‘Add assets’ is what we are interested in here. Essentially this enables to package files that are not traditional workflow assets (by traditional workflow assets I mean assets that are required for the workflow to run, for example macros or static data-sets).

To me this is a great feature, we can include documentation documents or videos that enable are users to understand how and why they can use the macro.

The proccess of adding files is simple.

  1. Navigate to the ‘Events’ tab on the workflow configuration panel.
  2. Make sure the ‘Enable Events’ box is checked
  3. Go to ‘Add’
  4. Select ‘Run Command’


  5. Navigate to the ‘Assets(s)’ tab
  6. Select ‘Add File(s)’ and add your files.


  7. Navigate back to the ‘Event’ pane
  8. Press ‘Okay’
  9. Go to ‘Options’ and ‘Export Workflow’, you will now see that any additional files you included can be selected to package within your .yxwz (Alteryx zip) file.

Now as you distributed this around your company via the Alteryx server, when they download the workflow/macro/application, the documentation will be extracted with the other required assets.


Once they import the file, any attached files will now be placed in the directory as given by the user! Excellent!


Using string calculations to create messages for NULL values

Guiding and ensuring your users interpret the data correctly is the key to a successful data visualisation.

So, question. What happens when your users come across NULL values in your visual, as in the instance below?


Best case, they simply understand that there are no recorded sales for that country during that specific month.

Worst case, they come up with some mind boggling conclusion that the Belgium store was forced to close due to declining sales in September 2015.

But how about we ensure the data is interpreted correctly through creating a combined lookup/conditional/string statement.


Tableau introduced a neat workaround in this Tableau knowledge base article, but there a re limitations. We may need to ensure that we distinguish between these NULL values and true zeros. True zeros may exist because we are rounding, or if you are presenting your data in 1,000s, 1,000,000s etc; they may also exist because you are working with a measure value that can be both positive and negative, such as profit, and in turn summing these values may equate to a true zero value.

The method outlined in this blog is a very simple tweek to that outlined in the tableau article, but instead of returning zero, we return text.


The lookup(sum([Sales]),0) is effectively evaluating what the value is of that specific cell (because we are offsetting by 0). Where are cells are empty, this with return the value NULL.

So by wrapping this formula with a isnull() we are quite simply returning a TRUE FALSE dependant on whether the vale is infact NULL or not.

By then using an IF statement we can identify what should happens if the condition is TRUE (i.e. it is NULL), where we return the string “No sales”, and where it is FALSE we return the sum of sales.

A note here is that the data type outputs from an if statement must return the same data type, and as a result we must change the datatype of sum([Sales]) calculation and to do this we simply wrap this in an str() – string- function.

Because our output is now a string and not a number, we as a result loose the ability to format the number, as a number (if that makes sense). This is why I include the round() function, but you may also wish to do some further manipulation such as adding a currency indicator, or thousands separators.


So can we use this to label NULL values on bar charts? The answer is yes, but it is not as simple as placing our new string field on the label shelf and the sum([Sales]) on the rows/columns shelf.


This is because the bars where there are ‘No sales’ don’t actually exist, so there is nothing to label, so we now need to perform a very similar calculation to return a value so there is something to label, I return the value 0, in order to build a mark which we can then label.



And now we have a labelled bar chart🙂



‘Chunking’ your Excel output in Alteryx – Part 2 – Introducing the ‘chunk’ macro

Starting where we left off.

A client had asked me to develop a method of automatically segmenting (chunking) our data by implementing a row limit.

It was now up to me to find a solution, and after gaining an understanding of the ways of which you can chunk data within an .xlsx output I was now getting down to trying to implement this knowledge.

And with that may I introduce the ‘Chunk Excel Output‘ macro.


As with all applications and macro’s that I build, I will always build out a traditional workflow first. I do this, a) to ensure that the idea is actually possible, and b) to avoid the complications that come in when introducing interface tools into the process. By doing the latter first, we may never get to a stage where we achieve the goal of point a.

My initial idea was to use the ‘Change Entire File Path’, which I knew would give the users flexibility to either output the chunks to different workbooks, or sheets, as desired.

The challenge here was, how do I acknowledge when a defined row limit was hit?

Firstly, something I knew I needed to be able to do was to acknowledge the row of each line, so the starting point was a Record ID tool. I then thought that I could use a multi-row formula tool to essentially create a condition which asks the question, does the record ID of the current line, meet the criteria of the current file (the current file being an incremental value which starts at 1).

This develops into a formula as (where the row limit is defined as 1000):

if [RecordID] = 1 then 1 elseif [RecordID]<=([Row-1:File]*1000) then [Row-1:File] else [Row-1:File]+1 endif


//Here we are defining whether the current row is the 1st row in the file, if it is then we will give it the [File] value of 1 (i.e. it is within our first chunk)

IF ISNULL([Row-1:File]) THEN 1

//If the record is not the first row in the file then determine whether the Record ID of the current row is less than the row limit, multiplied by the chunk number

ELSEIF [RecordID]<=([Row-1:File]*1000)

//If the previous statement returns TRUE, then we increment the chunk value, if not we keep the chunk value the same as the previous row

THEN [Row-1:File]

ELSE [Row-1:File]+1 ENDIF


We can then use a formula tool to append this value to a file path string…

“C:\Users\Ben Moss\Documents\Filename.xslx|SheetName”+tostring([File])

And now we have a different file path for each ‘chunk’.


And then as we use the ‘Change Entire File Path’ from our ‘File Output’ field, and run the workflow our records will now output accordingly.


And now that we have a method too create chunking by a row limit we can now bring our user input. In the case of this app, we simply allow the user to select their file path and chunking type, and manipulate the URL string accordingly, whilst we also allow them to overwrite the default value within the multi-row formula tool that gives the row limit.

I don’t want to go too much in detail in regards to the building of the app, but there are some great blogs out their in regards to the interface designer tools, including ‘One Step Closer to Alteryx Nirvana – Building Macros and Apps‘ by Michael Mixon, whilst Naledi Hollbruegge has posted a number of blogs that focus on specific interface tool on The Data School blog.

If you wish to see the detail of how the final macro was built then it is available to download.

Happy Alteryx-ing



‘Chunking’ your Excel output in Alteryx – Part 1

The main purpose of this blog is two fold. Firstly to discuss the configuration options available when looking to output your data as an xlsx file from Alteryx. Secondly (mainly a result of Excels 1,048,576 row limit), to introduce you to a method that segments your data into separate files/worksheets dependant on a user defined limit.


Lets start with the ‘Output Options’, there are four;

  1. ‘Append to Existing sheet’ – Alteryx will open an xlsx file and effectively union your data to the existing data under the file and sheet specified. Alteryx can error here for one of two reasons. Firstly your document must already exist, it will not automatically generate a new file if one does not exist originally.Secondly, the data that you are appending must have the same scheme as the data that already exists within that file.
  2. ‘Overwrite Sheet’ – Alteryx will open an xslx file and overwrite any data in the file on the particular sheet specified. Unlike with the previous option, in cases where the file did not exist previously, a new workbook will be created.
  3. ‘Overwrite File’ – Alteryx completely removes any file that exists under the same file path and generates a new file. Again if the file did not exist previously then a documented will be created.
  4. ‘Create new sheet’ – Alteryx will look to open an xslx file and add your new sheet as specified after the pipe within the file name2016-08-18_21-07-27

    In cases where the sheet existed previously you will receive an error ‘Sheet already exists’. If a file did not exist previously Alteryx will create a new document.

Okay so now we have covered the ‘Output Options’, we will now cover all things ‘chunking’.

First of all, what do I mean by the term ‘chunking’, essentially I mean segmenting our full data-set into smaller data-sets as outlined by a set of user defined instructions.

We can see the chunking options available to us with the .xlsx output by selecting the ‘Take File/Table Name from Field’. Here we again have four options.

  1. ‘Append Suffix to File/Table Name’ – Here we can append from a field a value with which we want to segment our data. With an xlsx output, selecting this type will output each value as a new sheet.Essentially we can create separate sheets for the different dimension members of a field. Lets take sample superstore for instance.Here I have set my file to write to the following path:’C:\Users\Ben Moss\Documents\Sample – Superstore.xls|Region_’

    At the bottom I have selected the ‘Append Suffix to File/Table Name’ chunk type, and have selected the Region column as the names to suffix onto my file path.


    As a result, when I open the outputted file, i see four tabs, one for each of the different dimension members that existed within the region field, ‘North’, ‘East’, ‘South’ and ‘West’, with these values appended to the sheet name given in the original file path ‘Region_’.


  2. ‘Append Prefix to File/Table Name’ – Acts in an identical manor as the above but this time adds the dimension members before the sheet name rather than after.2016-08-18_22-32-19
  3. ‘Change File/Table Name’ – Here irrespective of what value you give as the sheet name, Alteryx will simply output the sheet names as just the dimension members within the field specified.2016-08-18_22-35-13
  4. ‘Change Entire File Path’ – This one is pretty much what is says in the title, you must specify a field that contains an entire file path that determines where the file will output to. You can use this to write to either different sheets, as shown above, or to different files, unlike the previous three options. A practical example of the ‘Change entire file path’ could exist in cases when you want to input multiple files, manipulate them in the same way, before outputting them as separate files.In this usecase I have inputted 44 files using a wildcard input from a crime database. As part of the input configuration I checked the ‘Output File Name as Field’ to ‘Full Path’. After completing the manipulation I wanted (to remove crimes which did not have an ID), and then used a string formula to change the path so it was creating a xlsx file instead of a .csv.Replace([FileName],”.csv”,”.xlsx|Street”)

    Essentially I am removing the “.csv” from the original path, and replacing it with the “.xlsx” type, and also adding the sheet name as required with .xlsx file types.

    The workflow highlighted in this example can be found here.



So, lets get to the reason I wanted to learn all of this. Well, whilst working on a client project this week, one of the requirements they had was the output must be in an .xslx output. This immediately puts a limitation on the number of records we can output per file, as 1,048,576. The client then further stipulated that he would in fact like the row limit to be significantly lower than that (we settled at 500,000), for performance reasons.

Before I had time to learn all of this detail, we had to put a temporary measure in place, which ended up looking something like this…


Not ideal, firstly because I knew there was a method to chunk the data as outlined above (I just didn’t know how), and two, just because we are filtering to the different months within the data set this doesn’t necessarily mean the row limit won’t be exceeded. And in fact their was an instance where this was a case.

Damn, our temporary solution was now broken. It was time to learn and learn quickly.

In the second part of this blog I outline how I solved the problem outlined above by creating a workflow that restricts the number of rows that can be outputted to each file, and how I then developed this into a macro where users can define this row limit amongst a series of other options available to them.


Switching between discrete and continuous colouring

Ever wanted your users to have the ability to select whether they would like to colour their chart by either a continuous field or through discrete colouring but thought it wasn’t possible?

Well it is!

A use case came up last week and after a brain storm I managed to create this.


Note, this tip relies on a dual axis, so if your chart is already a dual axis then this is not possible (sorry :(), although an alternative would be to switch between your two worksheets dynamically, as outlined here.

  1. Create a parameter (or another element, such as a count) that defines whether the chart should be coloured in a continuous or discrete way.2016-08-15_19-10-49
  2. Create a calculated field which will return either the value [Sales] in my case, or NULL when each variable is selected.2016-08-15_19-13-25.png2016-08-15_19-15-43
  3. Drag these two pills onto the relevant shelf, in my case I wanted to create a horizontal bar chart, so placed the pills on the column shelf.What you will notice now is that as you switch between the values on your parameter, the charts will take it in turns to appear as your values switch between NULL and the appropriate measure.2016-08-15_19-14-28.png2016-08-15_19-14-57.png
  4. Now that we have two axes, we also have two marks cards, one for each axis that we can treat seperately. We can now format them as so. On the axis that appears when I select ‘Sales’ colouring (i.e. by my measure), i simply drag [Sales] onto the colour shelf.2016-08-15_19-19-33.pngWhilst on my secondary axis, I will drag my [Category] field onto the colours shelf.



  5. Lastly is some clean up work, hiding the NULL values indicator, creating your dual axis, and then synchronising your axes.Of course you cannot have your axes showing in this case because one of them will always show 0. In that case it is probably wise to label your chart as an alternative or make the values clear in your tooltips.

Indeed it is also possible to switch between chart types and measures using this method!



Tableau Tip – Using concatenation to sort values in tables and charts

I have a chart (not quite a dream), this chart is broken down by two dimensions ([Category] and [Sub-Category]), and a measure ([Sales]).


But when I sort sales I get a type of what I call, ‘nested sorting’, it is sorting sales, but it is sorting sales within each category (if you have a different number of dimensions, it will sort within the 2nd last dimension on your shelf.


But there is a way to prevent this, I simply need to concatenate the dimension members into one calculation, and place this as the first column, and the hide it.

To achieve this I create a calculated field, but you can achieve an identical result by combining your fields (I don’t know why, but I prefer the former).




Something that I often offer my users when such a requirement exists is a parameter that switches out the table with another sheet when they want to sort, using this great tip by Hashu Shenkar of The Information Lab.

I do this because I prefer the look and feel of the unsorted table, and how it doesn’t include unnecessary dimension members values as they are repeated down the table.