|
|
|
|
|
|
|
|
|
|
3/5/2010There was a question on the Alteryx forums today about reading multiple XLS tables. This is the kind of thing that the DynamicInput tool is designed for, except for 1 problem. There is no way in Alteryx to get a list of tables/worksheets from XLS. We could of course add a feature to Alteryx to do that, but customers don't have that ability. So what to do? How about use the RunCommand Tool?
So now we need a command line that gets the tables from an XLS file. Some quick Googling on line turned up some good VB script examples. The advantage of VB script is that you don't need any special developer tools on your machine. The following script (with no error checking) will take a OleDb connection string and return a list of tables:
Set con = CreateObject( "ADODB.Connection")
con.Open(wscript.Arguments(0))
Const adSchemaTables = 20
Set tables = con.OpenSchema(adSchemaTables)
Do
While
Not tables.EOF
WScript.echo(tables("TABLE_NAME"))
tables.MoveNext
Loop
 Again some Googling can figure out the OleDb connection string for XLS files. After this, it is just a matter of making a module that runs our little VBScript and reads all the tables in one stream. After this, it is a simple matter to take all the tables and read them into a single stream.
This only works if the schema's of all your tables are the same. If they are different, you might consider a batch macro which gives some more flexability.
Technical notes:
- As Provided, this module will only work in a 32bit version of Alteryx. The reason is that the XLS OleDb provider is only available in 32bit. If running a 64bit Alteryx, change cmd.exe to c:\windows\syswow64\cmd.exe in the RunCommand Tool.
- The RunCommand is doing another cool trick. It is piping the output to a file. If you notice, The above VBS example wasn't doing anything special with it's output. If you notice at the end of the command line there is a > temp.csv which causes the output to be written to the file named temp.csv.
The modules & scripts can be found here.
3/1/2010
Adam Riley talked about the Regex parse documentation seeming incorrect. His understanding of the Regex Tokenize is in fact correct, our help is clearly missing an example and potentially misleading as well. It is however, fairly straightforward to tokenize on a delimiter or set of characters. The part that is missing from the documentation is that the Tokenize extracts either the entire match or the 1st marked part of a match. This allows you to extract just part of a match.
Since the tool outputs the part that matches, we have to mark the part in between the delimiters for output. We have to be careful here if we are matching everything to either exclude the match or do a non-greedy match. The non-greedy match ends the match at the 1st possible place a match can end, whereas a default match will take the longest match possible. That might include multiple delimiters. We then want to find out match ending with a delimiter, or the end of the line. Matching to the end of line is important, otherwise you might drop your last token.
Here is a regular expression that tokenizes based on commas.
(.+?)(?:,|$)
- () - This is simply creating a marked expression. The part enclosed is what will be output in the separate fields or rows.
- .+? - A non-greedy match of 1 or more characters. Since it isn't greedy, it will terminate at the 1st match of what follows.
- (?:) - A non marking group. We need this so that we can select between our delimiter and the end of line ($).
- ,|$ - matches wither a comma OR the end of line
To get this far would be really easy to do with the Text To Columns tool. It only gets interesting when we do more. Now that we have the pattern down for Tokenize, it is really easy to change it to match other things:
etc... You can see how easy it is to create a custom tokenizer. A module to demonstrate and start playing with these regular expressions can be found here.
On an amusing note (or not so much): while writing this blog I found a bug in the tokenize method. It will let you create a 0 character token which of course generates an infinite number of results. Not a good thing. 2/26/2010First, a big shout out to Adam Riley and his UK Alteryx Blog! Nothing gets me going as a developer more than seeing users fired up about using the product. Thanks Adam for your enthusiasm.
Our annual conference Extend2010 is just around the corner and Ian (our Chief Architect, Core Web) and I are planning the Developer Summit. One problem that we are having is that everyone seems to have a different view of what a developer is so our audience may be very diverse. For the purposes of these sessions, we are preparing for everyone from users just starting to create macros and wizards in Alteryx, to web developers looking to deploy custom services to hard core C++ programmers looking to extend Alteryx in ways we never imagined.
Creating an agenda that will keep everyone satisfied has proven to be a challenge, but we hope that we have something for everyone. Our preliminary agenda is:
- Overview of the Developer Tools category in Alteryx - Ned (CTO)
- Writing Effective Macros - Ned (CTO)
- Batch Macros: Why & How - Rob (Senior Developer, Core Engines)
- Consuming Alteryx Wizards from C# - Linda (Senior Developer, Core Engines)
- Alteryx Connect Web Services: Architecture & Futures - Ian (Chief Architect, Core Web)
- Consuming Alteryx Connect Web Services from JavaScript and C# - Ian (Chief Architect, Core Web)
And finally, if there are any hard core developers interested, we will have a bonus session in the solution center from 1pm - 2pm:
- Alteryx SDK: Writing tools for the Alteryx toolbox (C++ && C# skills required) - Ned
Every session will include ample time for Q & A. Our hope is that we will have enough content to keep the interest of everyone from experienced Alteryx users to hard core developers. If there are any other topics that people want to cover, all of us will be available in the solution center over the course of the conference. This will be a technical session, so for people not already familiar with Alteryx, you may be better off attending the Product Training that is happening at the same time.
Unfortunately, anonymous comments are not yet working on this blog, so if you have any comments about the agenda or requests for topics you would like to see covered, email me at nharding@extendthereach.com.
1/19/2010
I got asked the question of how to generate random numbers with a normal distribution. A quick search online provides all kinds of formulas, it is a simple matter of picking one to implement. I could of course implement this in the formula library in C++, but that doesn't provide instant gratification, so off writing a macro I go. Some of the functions are iterative, which would be slower and more difficult in Alteryx, so I chose a simpler formula. The formulas online also produce a normal distribution with and average of 0 and a standard deviation of 1. Depending on the data you are trying to model, you probably want to change that. Rather than make the use do it, I added some parameters in the macro.
Finally, anytime you write a macro it is nice to test it. I wrote a quick module to generate 1,000,000 random #'s and test their average, StdDev, and also produce a chart so you can visually confirm that it is a norm distribution. The results of that are:
|
Number of Iterations Tested: 1,000,000
Average(should be close to 100): 100.02
Standard Deviation (should be close to 15): 14.98 |
|
|
My favorite part of this is I used the chart that the test module produced as the icon for the macro. Kind of recursive. Anyway, now generating random numbers with a Gaussian Distribution is as easy as dragging out a macro. The macro and test module can be found here... 1/7/2010To start at the end, the source code associated with this article can be found here. The projects are all built with VS 2005.
I have received a bunch of questions about using the Alteryx API lately. It is mostly straightforward, but it is can be confusing how to find the latest installed Alteryx directly and properly wrap calls from C#/,Net to the Alteryx API. In order to simplify using the API, I wrote a simple wrapper that not only wraps the Alteryx API, it also will find the latest installed version and link to it without having to do anything special. The interface is compiled for any CPU, and will detect if you are running in 64bit or 32bit and find the appropriate Alteryx. It you are running in 64bit and you only have a 32bit Alteryx installed, it will fail. All failures are thrown as a AlteryxApiException. In order to use that Alteryx API, all you need to do is to include AlteryxAPI.Net.dll as a reference in your project. That's it, just start calling the API and it will deal with finding the currently installed version.
In addition, there is a new API sample application. It just implements a Mortgage Calculator wizard. It shows running a custom interface on top of a wizard. It allows a user to solve for any of the common Mortgage parameters. It does it all via an Alteryx Wizard. It also shows the version # of the Alteryx that it found installed.
The Interface of the sample app | The Alteryx wizard behind the scenes | 
| 
|
This application does show one advanced API feature. There is a special tool that can be used to send output directly to the API without going through a file. This application uses that feature to directly populate the result into the form. This example shows just how easy it is to link to the Alteryx API to perform a complex operation. There are only a few lines of code that deal with the Alteryx API at all.
In addition to creating the new DLL wrapper, there is an associated help file:
Update: There was a small bug in the Alteryx wrapper from this article that could generate an Null Reference exception if you didn't have the correct Alteryx installed. (32 vs 64 bit). The code (found here) has been updated. Also, although the .Net wrapper will work with Alteryx 4.1, the associated sample uses new features in Alteryx 5.0 so it will not work in 4.1.
12/16/2009Before I get to today's post, I want to talk a little about Bill's recent post. Polar graphs are not for everyone, but what is really interesting about that post is the technique of using the map tool to make custom charts. He used the map tool in a way that had nothing to do with mapping, it was used exclusively as a rendering engine. Anyway, back to today's topic...
A gravity model is a simple model of the flow of trade from demand (the customers) to the supply (stores). I wanted to make a macro that implements the gravity model to make it easy to reuse. Just to review some simple rules of building macros & wizards: The 1st step when building a macro (or wizard) is to build a standalone module that works - so I spent some time building a module and documenting the module. Once that is done, Figure out the list of questions you want to ask. In my case it was some simple gravity model parameters:
Once you know the questions to ask, identify the tools in the module that need to be changed. It is always a good idea to go into the annotation of each tool you want to change and set a descriptive name (it will make it easier to find later):
Now go into the macro settings and set all the appropriate information into the tools:
Since we named the tool, it is really easy to find in the list. In this case we are updating the Friction of Movement (exponent) value in the formula. Once you have all your actions figured out, you can go ahead and configure your inputs and outputs and you have a pretty easy macro to use. I always like to put a new icon on my macros so I can remember what they do.
Now that the macro is so easy to use, it's really easy and quick to experiment with adding or removing store locations and you can see how it affects your network. Wouldn't it be cool if there was some automated way to do that? Oh well, that will have to wait.
This model is of course very simplistic. We have a whole bunch of companies that use Alteryx to produce MUCH more interesting models than this. I will be trying to put together a list of companies and their specialties that can help people build all kinds of Alteryx modules.
The modules in this post can be found here. Note, this macro requires Alteryx 5.0
12/8/2009Now that Alteryx 5.0 is finally out the door, my team and I finally have a chance to breath. Once again, the team did an amazing job. I believe that Alteryx 5.0 is the best release yet. The team that brought this release involves a whole lot more than development, so the list of people to thank is getting bigger all the time.
1st, a big thank you to the development team. I could not do this without Linda, Rob & Kurt. Linda gets extra props for doing anything and everything for the last few weeks to get the product tested, built and out the door. Rob gets extra props for fixing the last minute install bug after we thought we were done and the rest of us were on vacation. Thank you, you guys make me look good.
How on earth could we do this without the products team. Tara came back from having a baby straight into a stressful release cycle and was amazing. Nothing would happen in Boulder without Amy. She is the anchor that keeps the rest of us going. And Rob, the newest member of the team, had quite an awakening about just what it takes to release a huge package like Alteryx, but jumped in with a great attitude and was really helped with testing. Thank you.
Another big thank you to everyone who helped test. The data and professional services team are HUGELY appreciated for helping with the regression tests. In particular a big thank you to Bill - without your help the Calgary link tables would be much worse. Lots of other people in other departments help with our testing blitzes at the end. It is them you can thank for running such a quality product.
I would also like to thank the Web Core team, in particular Ian. They are becoming a major consumer of Alteryx and driving us to improve in all kinds of subtle ways. It is a pleasure working with you guys. Many of the improvements in the next year will be driven by them.
As they say, it takes a village to raise a child. Similarly it takes a whole company to produce a great product. SRC has a fantastic group of talented people and it is a please working with all of you. Thank you for your time and efforts. We couldn't focus on making a great product if we didn't have such a great team backing us up.
And last, but certainly not least, I want to thank all of our users, in particular our beta testers. I won't name any of you, but without customers, Alteryx doesn't exist. For Alteryx 5.0 we got the best feedback from beta testers ever. We also have been getting more and better feedback from other customers. Every one of the new features is in response to something you guys requested (ok demanded).
Thank you,
ned.
12/7/2009In mathematics, the polar coordinate system is a two-dimensional coordinate system in which each point on a plane is determined by a distance from a fixed point and an angle from a fixed direction.
We can create a polar coordinate system in Alteryx by taking an input file with a distance and degree for each of the 360 degrees in a circle and using our Mapping Tool creatively we can emulate polar graphing.
Sample:
Distance | Degree | 0 | 0 | 0 | 1 | 0.5 | 2 | 1.3 | 3 | 2.6 | 4 | 4.4 | 5 | 7 | 6 | 10.4 | 7 | 15.5 | 8 | 23.2 | 9 | 24.6 | 10 | 20.3 | 11 | 19 | 12 | 19.8 | 13 |
The Alteryx module will determine the max distance value from the input file. With the max distance we can build the distance rings from the center (0.00 Lat and 0.00 Lon) in increments of 10 using the generate rows tool. The outer Ring is 0 distance with the next ring toward the center as distance of 10.
Using the formula tool with the input data from above we determine the distance from the center for each value by taking the Max_Value – Distance. So that when we plot the 0 degree and 0 distance it is located on the outer ring at 0 degrees.
The distance from the center for each direction is used as input into the GetOffsetPoint macro which calculates the actual point in space from 0.00 lat and 0.00 lon, the Offset Point.
Using the Poly-Build Tool Sequence Polygon with the Source Field the Offset Point and the Sequence Field each of the degrees starting at 0 and ending at 360 the graph is created.
The Vertical lines for the graph are built using the GetOffsetPoint every 15 degrees with the distance of the Max_Value calculated above. The Poly-Build Sequence Polyline actual generates the lines for the graph from the Offset Points.
We also create the Label point for the rings using the GetOffsetPoint with increments of 10 for the distance and 0 for the degrees.
Finally the layers are all joined together and Plotted using the Map Tool to view the results below.
A good example of a practical application for Polar Graph is graphing signal strength around a tower.
For example module and Data click here. Download the Polar Graph ZIP file. 11/30/2009
It is official... Alteryx 5.0 was released on Friday November 20, 2009. It is on its way to our devoted users and loaded with new features and functionality.
I will be conducting the What's New in 5.0 webinar tomorrow, Tuesday December 1 at 2pm Mountain Time. If you haven't done so already please register as I will be touching all the new stuff and directing you to where these gems are located in the program. This session will be recorded so you can play back at your leisure. To register click here.
The Customer Advantage group has already scheduled instructional webinars on the new features of Alteryx 5.0. You can register for them here.
We've been blogging about some of the latest features since the summer. Below is a list of the highlights of this release and hyperlinks will take you to the posts on those topics:
· Dynamic Select and Dynamic Rename Tools – these new tools enable developers to create more adaptable wizards and work with a wider variety of data.
· Report Formatting Tools – two new tools in the reporting category – Arrange and Overlay – provide greater flexibility in creating customized reports. The Arrange Tool enables transformation and rearrangement of data fields for more meaningful presentation of information. The Overlay Tool allows report content elements (e.g. maps, tables, images, etc.) to be overlaid on top of each other.
· Reporting Email Tool – this tool creates automated email attachments of Alteryx module outputs, which then get directly sent to the defined recipients.
· Batch Macro - enables automated program looping within a single module. Allows for target analysis of individual records/groups that need to be isolated from each other.
· New Macros – six new macro tools have been added, including several different statistic calculations and a macro that returns a count of how many records are going through the tool.
· Formula Library Additions:
-
Spatial Functions Category – over 20 new spatial functions have been added including calculations of area, bounding rectangle, centroid, distance, length, and others. These spatial functions can be utilized anywhere in Alteryx where formulas are used.
· Finance Functions Category – financial analysis calculations (e.g. NPV; IRR; etc.) can now be natively added to Alteryx analyses (no need to export data to other applications to perform these calculations).
· MetaCarta GeoSearch News Wizard – a new wizard is being added that will launch searches of MetaCarta’s vast library of news feeds and will return the latest articles, blog entries, etc. for any topic in any defined geographic area.
· Calgary Table Linking – new capability to link multiple Calgary data tables together for querying. Build one query that accesses multiple tables and brings back a combined result.
· Module Dependencies – easily see and edit module data dependencies/paths for easy sharing and distribution of modules from one user’s desktop to another.
Hope you enjoy the improvements we made to Alteryx. As always we welcome your feedback!
Tara
10/27/2009It's been a long time since our last blog post. The core team is extremely busy working towards Alteryx 5.0. I hesitate to give an exact date, but we are getting very close to a code freeze with a release a few weeks after that.
One of the issues that has come up in our testing is comparisons to NULLs in formulas. For the purposes of simplicity, we will be focusing on numbers, but strings have all the same issues and produce the same results. Think about the following examples:
1<Null()
0<Null()
-1<Null()
Null()==Null()
Null()<Null()
Null()<=Null()
In the past, we made the decision that Null()==Null() should be true. Without that being true, it makes it difficult to compare streams of data. Many people would argue that Null()!=Null() and in an ideal sense, I would tend to agree with them. The problem is that 2 different fields can be Null() for different reasons and may not be equal in any real sense. The problem with ideals is that they aren't always intuitive. Users expect to be able to compare fields from 2 different data streams for equality. Implementing some Platonic ideal of "right" that confuses users never helps.
We recently found some bugs in the <, <=, > and <= operators with regards to Null() and we were forced to make the decision that nothing is greater or less Null(). If there is a Null() on either side of a < or >, than the answer is false. Clearly Null()<Null() is also false. With <= and >=, if there is a Null() on either side, the only way it can be true is if there is a Null() on the other side - because Null()==Null().
It does get weird though. If 5<Null() is false and 5>Null() is false, doesn't that imply the 5==Null()! Obviously having Null() values puts the comparisons outside the normal rules of logic. Another weird one: Null()==(Null()+1) Since (Null()+1) results in Null(), the result is true. Another argument that Null()==Null() should probably be false in an ideal world. Sometimes you have to compromise and be less "right" to give users what they want. |
|
|
|
|
|
|
|
 |
|
|
|
|