The Alteryx Team Blog is being retired. Our marketing department has given us a shiny new home on alteryx.com, and the blog has been renamed “Alteryx engine works”. I welcome you over there with a new post: New in 6.0: Document Constants.
We finally have comments working, so feel free to post feedback (over there, not here). Also, you will need to re-subscribe to the blog via RSS if you were subscribed to the old one. You can either subscribe to just this blog here: . Or alternatively you could subscribe to all Alteryx blogs in a single RSS here: .
So head on over there and subscribe to the new blog. There should be lots of new content in the next month with the 6.0 release coming up. And please leave your comments.
We got a question via the forums:
I'm trying to output to Excel, but a number of fields in my data contain only Null values and I want to exclude these. Is there any easy way to do this? The data changes every time I run the module, so the fields that contain only nulls will not be the same every time. I don't want to have to keep changing the module to suit the data I am inputting.
Thanks in advance.
This is another good example of something that there is no direct tool to accomplish, but is totally possible to create a tool as a macro. Thinking about this a second, it becomes obvious that we have to go through the data to figure out which fields to set to NULL. The Summarize tool has a mode for counting the number of non-null fields, but it requires you identify which field ahead of time. Since we want to make it as generic as possible, this doesn’t quite work. If we use a Transpose tool 1st, it we can easily group by field and see if there are any non-null records in each field.
Now that we have a list of fields to remove (the fields with no non-null values), how do we do it? The developer toolbox comes in handy here. There is a tool for Dynamically selecting fields, but that doesn’t take any data from rows, so how to we get the data in there? Since that tool can look at field names, if we could change the field names to mark them to be removed, our problem would be solved. Fortunately, we have that tool too: the DynamicRename tool. If we take the field names from rows on the right, we simply have to make a formula that renames the field.
Once again, this is an example of something that is relatively easy to do in a few tools. Once a macro is built, it becomes as easy to use as any of the built in tools. The macro and a test module can be found here.
1st – I want to give a HUGE thank you to everyone who contributed. Seeing the disabled athletes persevere, Clarissa in particular, it was truly a worthwhile cause. Thank you so much, you made this possible for all of us.
The team consisted of myself, Catherine Shenk (from Marketing), Nino Pozgaj (inside sales), Nino’s daughter Clarissa who is in a wheelchair and has limited use of her body. Jeff from World TEAM Sports connected us with Matt Nyman, a “disabled” ex Army Ranger missing one leg below the knee and limited function in the other. I put disabled in quotes because he climbed Denali a few weeks ago. He is one tough hombre.
Long story short, we did awesome. Not only did we finish, we finished 3rd out of 7 teams! We functioned as a team incredibly well. I can’t begin to pick out a strongest member of the team, because everyone was so strong. Nino and a friend constructed a bike trailer/chariot for Clarissa that mostly worked awesomely, although it was very heavy. None of us knew the difficulty of terrain we would be travelling through. Incredibly tough trails that were much narrower than our wheelbase. It was a ton of work, but totally worth it. The race consisted of rafting (up to class IV), biking, hiking, a zip line, navigating, route finding, etc. It was a huge challenge.
One of the best aspects of the race was our support crew. Nathalie, Maureen, Chad, Peter & Wayne all came out with us and we couldn’t have done it without them. From making dinner, to fixing the chariot, to meeting us in totally unexpected places with cowbells, they kept us going. We could not have done it without them. A huge THANK YOU to them.
In many ways, this event has changed how I think about Alteryx (the company.) Being able to be there for Clarissa & Matt and support them, us and the entire World TEAM Sports community makes me feel like we are really doing something worth while. It was only 5 of us in the race, but we really did feel the whole company behind us. Thank you.
Bike to work day was especially cool this year with the spiffy new jerseys.
I find myself occasionally using alteryx to generate XML from tabular data. In the past I have always created solutions specific to the data at hand. I have decided that it is time for me to create a generic tabular to XML alteryx wizard.
I tried to make this as flexible as possible, so there are a few complexities and options to discuss:
Here's how it works
Each level of XML is created from one or more columns of tabular data.
If you wish to have multiple attributes for a level, you will need to group columns by name. The columns "Category", "Category:ID", "Category:isValid" will produce an xml level where each node will have "value", "ID" and "isValid" attributes. Notice that if you don't specify an attribute name ("Category" vs" Category:value"), then "value" is used by default.
The first attribute for each level will be used to determine the structure of the XML. That is to say that the first attributes for the children of any given node must be unique.
I/O files: The user is asked to point to both a tabular data source and an output file (.xml).
Top level node type: The user must specify a node type for the top level of XML.
Node types: The user can choose to use the column names from the data source as the node types or specify a generic type to be use throughout the file.
Don't remove blank nodes: If left unchecked, nodes with blank "first attributes" will be removed and children will be moved up the branch while maintaining their type.
download the example tabular data gathered from http://www.aou.org/checklist/north/full.php
download the example XML output
On Friday, June 25, athletes of all abilities will tackle the Rocky Mountains in the World TEAM Sports Adventure TEAM Challenge. An idea conceived by Erik Weihenmayer, first visually impaired individual to climb Mt. Everest, the Adventure TEAM Challenge is a multi-day, multi-sport TEAM-building wilderness adventure. Many of you who attended the Extend 2010 conference last March had the pleasure of being inspired by Erik’s keynote address at that event.
Erik invited Alteryx to participate in this year’s Adventure TEAM Challenge, and we accepted that invitation. The Alteryx team is made up of five members, three of whom are Alteryx employees: Ned Harding, CTO; Catherine Shenk, Marcom Manager; and Nino Pozgaj, Manager, Sales Operations.
Nino’s fifteen-year-old daughter, Clarissa, is the fourth team member. Clarissa is wheelchair-bound with Arthrogryposis, also known as Arthrogryposis Multiplex Congenita—a rare congenital disorder that causes multiple joint contractures and is characterized by muscle weakness and fibrosis.
Today, our fifth member was confirmed. Matt Nyman, a twelve-year Army veteran who lost his right leg below the knee and suffered severe damage to his left leg and foot serving in both Iraq and Afghanistan, will join the Alteryx team.
The Adventure TEAM Challenge, like Alteryx, is all about pushing the envelope on problem-solving while celebrating true diversity and increasing our effectiveness in working together as a team. Please check out the Adventure TEAM Challenge website to find out how this amazing event is changing the lives and dreams of able-bodied and disabled adventurers alike.
Please contact Ned Harding (email@example.com) if you would like to honor the Alteryx team with your charitable contribution. You will be supporting an organization that works to make life better lived for every special participant they can reach.
Sometimes data comes in to Alteryx in an unexpected code page. One example recently is Tele Atlas started shipping their data in SHP files that are UTF-8 encoded. This is a little odd, because ESRI publishes a spec for SHP and it doesn’t include support for UTF-8 and the DBF file that ultimately holds the data doesn’t officially support it either. It seems that ESRI is playing fast and loose with their own file spec. In the future I am sure that we will figure out how ESRI has extended SHP so we can read these files directly, but in this case it wouldn’t help because Tele Atlas didn’t see fit to include the CPG files along with the SHP files that describe the text encoding.
Anyway, you get data in Alteryx that looks like: Saint-FÃ©lix-d'Otis where what you really want is Saint-Félix-d'Otis. For Alteryx 6.0 we added a pair of functions that make this translation really easy. ConvertToCodePage and ConvertFromCodePage. In order to convert all the text fields in a stream from UTF-8 to a valid string, you only have to use a single MultiFieldFormula tool with the formula: ConvertFromCodePage([_CurrentField_], 65001) Easy, huh?
Now the problem with this is that our customers don’t have Alteryx 6.0 yet, so it would be really nice if we could come up with a macro to do the same thing. CSV files support code pages, so how can we leverage them to translate text fields for us? Simple, we’ll use a RunCommand tool to write a temporary file and then read it again. A slightly undocumented feature of the RunCommand tool is that you don’t need to run a command at all. You can use it just to write a file and then read it.
There is a problem though – CSV files don’t support all field types. Specifically it doesn’t support spatial objects which are guaranteed to be in a SHP file. We can use the new in 5.0 DynamicSelect tool to select out all the string type fields, do our processing and then join all the field back together. The other problem with CSV is that we lose the field sizes and such, so we can also include an AutoField tool to repackage the fields into the smallest size appropriate.
Once this is all done, it is an easy job to make a macro out of it. This makes a single tool that you can add to any stream to convert all the text fields from narrow character UTF-8 to standard Unicode.
The files and macro can be found here.
I recently had a client ask if there was anything in the Alteryx API to help figure out what the questions were in a wizard. Since the wizard is an xml file, we could just parse it with our favorite xml parser and look for the AlteryxDocument\Properties\RuntimeProperties\Questions tag. In fact, this is what I recommended to the client. But surely there must be an easier way. After all, this is something that Alteryx needs to do in order to build the user interface when a wizard is run. Fortunately Ned provided me with the answer: the AlteryxDocument.dll.
The AlteryxDocument.dll is the .NET assembly that the Alteryx GUI uses to read and write Alteryx document files. It basically knows everything there is to know about an Alteryx module. For the purposes of this post, we'll focus on the question at hand – how to determine the questions in a wizard.
The main point of entry is through the Document class, which encapsulates all of the document reading and writing functionality. It has a constructor that takes the path to a module file:
Document module = new Document(path);
The next thing we need to know is what type of module this is. The Document class has a Type property that returns a DocumentType value. This value can be Module, Macro, or Wizard. In our case we are interested in only Wizard document types.
if (module.Type == Document.DocumentType.Wizard)
Now that we know our module is a wizard, we can get the list of questions out of it. The questions are exposed through the RuntimeSettings property. The Runtime class provides access to the information that needs to be handled when a module is run, and it has a Questions property that returns the list of wizard questions. There are several different types of questions that can be part of a wizard. When you click the Add button on the Wizard Design dialog, you can see the list of options.
Each type of question has a class associated with it: QuestionBoolean, QuestionBooleanGroup, QuestionDate, QuestionFileBrowse, QuestionFolderBrowse, QuestionLabel, QuestionLabelGroup, QuestionLink, QuestionListBox, QuestionNumericUpDown, QuestionRadioGroup, QuestionTab, and QuestionTextBox.
All of these classes derive from a base class called QuestionBase. This class provides support for all of the common question functionality, while each subclass provides the specific functionality for its type.
Several of the question types are not actually questions, but collections of other questions instead. QuestionBooleanGroup, QuestionLabelGroup, QuestionRadioGroup, and QuestionTab are all examples of this. In order for us to get the entire list of questions, we will need to recurse through these groups. Fortunately, the QuestionBase class exposes two properties to help with this: HasChildren and Children.
The following code snipped demonstrates using these properties to fill a ListBox with the names of all of the questions. I've added a groupIndex property to add some indentation in order to highlight the nesting of questions within the question groups.
private void AddQuestionsToList(List<QuestionBase> questions, int groupIndex)
StringBuilder spacer = new StringBuilder();
for (int i = 0; i < groupIndex; i++)
foreach (QuestionBase question in questions)
QuestionsList.Items.Add(spacer.ToString() + question.LongDescription);
The results of running this on the MortgageCalculator sample wizard looks like this:
Comparing the results to the wizard GUI produced by Alteryx, it is easy to see that we have the information we need to build our own wizard interface. Other information, such as default value, can be obtained from the question subclasses.
If you would like to work with this interface, you can find the sample code (written in Visual Studio 2005) along with the AlteryxDocument.dll here. Keep in mind that this version of the AlteryxDocument.dll is currently under development and is likely to be newer than the version that your install of Alteryx includes. This means that if you write any module files through the interface, you will not be able to open them in Alteryx until the next version is released. Finally, your project will need to include a reference to the SrcUtils_DotNet.dll which you can find in your Alteryx 5.0 install folder.
First: I am being dragged kicking and screaming into the 21st in the world of Twitter. You can find me at AlteryxNed. Other Alteryx people are up there as well: AlteryxDean, AlteryxKim,& GeoSinam among others. I will be posting there at least a few times a week and you might be the 1st to know what I am working on.
Onward to the Adventure Team Challenge: we have picked 4/5th of our team. It will consist of me, Catherine Shenk & Nino Pazgaj as well as Nino's daughter Clarissa. Clarissa is in a wheelchair and will be one of our 2 disabled participants; we are still looking for 1 more, hopefully not so fully disabled.
The support has been incredible, both inside and outside of Alteryx. We have raised $2,355 – with the Alteryx match $4,710! Thank you everyone who has pledged. We are well on our way to our $10,000 goal. Many people inside have volunteered to come with us and support us in various ways, this is going to be very exciting. I only hope I can keep up with Nino & Catherine.
Today we are going to take a break from technical posts about Alteryx. Work, software and data is a huge part of our lives, but at the end of the day, there are some things that are more important.
At our Extend conference this year, Erik Weihenmayer was our keynote speaker. I think I can speak for most everyone there to say that we were all very moved listening to him speak. I can’t even fathom climbing Mt Everest, let alone doing it blind. Erik is an amazing individual and left all of us wondering how we could do more for the world.
After the conference, we got an email from Erik inviting us to participate in his Adventure Team Challenge. It is an adventure race involving a mixed team of able and disabled athletes in the mountains of Colorado. Of course we had to say yes, even before we knew what we were getting in to. Take 5 minutes out of your day to watch the video from last years race. This really is an amazing event with some amazing people.
So now we get to the request. You knew it was coming, didn’t you? Putting on an event of this kind takes a lot of money. World TEAM Sports is doing an amazing job bringing people who thought their athletic lives were over into the wilderness. We have to raise a bunch of money to participate in this event. So, I am looking for donations to World TEAM Sports to support our team. For any donations over $75, I will send you a really cool Alteryx bike jersey. I’ll even give you a jersey for a $65 donation if you pledge it by April 19th – please tell me your size. Contact me at firstname.lastname@example.org to make your pledge. WTS is a non profit, so all your donations are tax deductable. I am sure for donations large enough, we could arrange free seats of Alteryx or just about anything else. ;-) Really anything will help for this great cause that I really believe in.
To Make a donation:
Email me at email@example.com to pledge and we can work out the details, or:
Make out a check to World TEAM Sports and mail it to:
3825 Iris Ave Suit 150
Boulder, CO 80301
Don’t forget to include your Jersey size: Women's or Men's S-XL