Honeycomb's big...yeah yeah yeah!

I had seen some maps that used a hex grid overlay, rather than rectangular. I admit that I became fascinated by hex grids. I also admit that my fascination was nothing more than visual. So - I did some internet research in an attempt to get a quick fix. To clarify, I wanted the formulas so that I could generate the hexgrids within Alteryx. I found no quick fix.

Necessity is the mother of invention, I needed it, I reinvented it with Alteryx. I was able to generate, entirely, the hex grids within Alteryx. What I was missing was the math...always the math. You probably remember thinking "when will I ever use sine and cosine?" To do the hex grid, on the globe, you need formulas like: 3963 * ACOS(SIN([C_LAT]*............

Here is an example using the hex grid within a Wizard. The Wizard calculates expected ATM transactions utilizing:

• the hex grid overlay
• another process that creates ATM nodes based on proximity
• household level data capture using the grids
• a spatial interaction model

The Alteryx run was completed in 27.7 seconds and included:

• 2,409 hex grids (1/4 mile)
• 133 spatially, interacting ATMs combined into 60 nodes
• 95,357 households
• Projected ATM transactions for site utilizing the aggregation of each Hex grids' probable transactions for the site

Get creative, do some reading and take a chance. Let me know what you think!


Andy Moncla

New Alteryx 5.0 Features

Now the Alteryx 5.0 has been released, I'd like to comment on some of the new features and functionalty. Here's a list of the new tools and enhancements below:

New Tools and Macros

• 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 – there are two new tools – Arrange and Overlay – that will provide greater flexibility when generating reports. The Arrange Tool allows data fields to be rearranged and transposed in a report and the Overlay Tool allows report content elements (e.g. maps, tables, images, etc.) to be overlaid on top of each other.
• Reporting Email Tool – Automatically sends an e-mail with attachments. This will save a number of steps when having to notify a number of individuals that a process is complete while also providing them the output. There is a post on how to use this tool on the Alteryx Forum:

How To Use the New E-Mail Reporting Tool

• Batch Macro - I am excited about having a looping functionality within Alteryx - look for a post on this soon.
• New Macros – there a six new macros included in this release, from different statistic calculations to a macro that returns a count of how many records are going through the tool. Many of these were discussed in my interview with Dean Stoecker a couple of posts back.

Two New Formula additions

• Spatial Functions – over 20 new spatial functions have been added which can now be formulated and then used within your calculations.
• Finance Functions – financial analysis calculations can now be performed within Alteryx without exporting data to another tool for processing.

New Wizard \ Functionality

• 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." This quote is from the official feature summary- This is the big hook for me (even bigger than Batch Macro)! Now you'll be able quickly gather "real world" information about a particular site or area and incorporate it within your decision process.
• Module Dependencies – quickly change the module data dependencies/paths for easy sharing and distribution of wizards/modules to the masses.

New Calgary Functionality

• Calgary Table Linking – now you can link and query across multiple Calgary data tables - Sweet!

There are so many new ideas as to how to extend my current modules\macros\wizards using the new enhancements in 5.0, so look for many of these features to be incorporated into upcoming posts.

Till next time, enjoy!

P.S. Still working on part two of my interview with Dean about Alteryx Connect - stay tuned!

Wishing All Alteryx Users a Happy T-Day! (Using the MultiRowFormula Tool)

I wish everyone a happy and thankful Thanksgiving today! Please take the time today to think about all that you are thankful for!

Just a quick post in between posts on  the interview with Dean Stoecker - it takes a while to transcribe what Dean said to the blog so bear with me on part two of the interview.

The module below is an basic example using the MultiRowFormula Tool. Simliar to the Formula Tool yet very unique in its own right, this tool is used to dynamically create new dataset fields based on the use of the pre, current and post row values to formulate the new field values.

As a common example of this, the module I've provided calculates the incremental population percent for each row given the population count for a few select blockgroups. After reading in the population data, I rank the individual blockgroups population count in Descending order on with the Sort Tool. I do this so it is easily determine the largest blockgroups that contain a specific precentage of population.

Here is how I configured the MultiRowFormula Tool below:

I created a new field called INCPOPULATION  (short for Incremental Population) that consists of following the formula:

[Row-1:INCPOPULATION]+[Row-1:POPULATION]

This formula adds the value in the current row INCPOPULATION field to the record POPULATION value of the previous record and places that value in the INCPOPULATION field of the current record. Clear as mud, right? This probably makes more sense once you see the end result, a sample of which is shown below:

I then used the Summarize Tool to sum all values in the POPULATION field to get the total population (Sum_POPULATION) and append it to each row using the AppendFields Tool, and added another MultiRowFormula Tool to calculate the total incremental percent population using this formula:

[IncPopulation]/[Sum_POPULATION]*100

Which provided the new PERPOPULATION field value for each row as shown:

Note that the PERPOPULATION field could have easily been created using the Formula Tool, I only wanted to demonstrate how this could also be accompished easily with the MultiRowFormula Tool as well. Now can quickly determine which blockgroups contain, say for example 80% of the population, or any percentage for that matter.

Here is the module I used for quick reference:

This example barely touches the surface of all that you can do with the MultiRowFormula Tool, yet it is the holidays and I hear some pumpkin pie calling my name! =(;-))))

Till next time, enjoy!

Interview With Dean Stoecker, President and CEO of SRC

I recently was granted the opportunity to interview Dean Stoecker, President and CEO of SRC for the All About Alteryx blog. I specifically asked Dean about the new features and functionality in Alteryx 5.0, which will release in the next few weeks. I also asked Dean about what is next for SRC and the Alteryx Connect product. So I've split the interview into two blog posts, one about Alteryx 5.0 and another about Alteryx Connect. Today's post is all about Alteryx 5.0. I found it very interesting to listen to Dean talk about the approach taken to determine which new functionality is added into a release and about some of the new features; there were just too many to discuss completely in this format. Here is what Dean had to say:

Ron: Hi Dean, and welcome to the All About Alteryx blog.

Dean: Good to be here!

Ron: Dean, Alteryx users, me included, are eagerly awaiting the release of Alteryx 5.0. Can you give us a preview of what we will see in the new release?

Dean: Gosh, there are so many new features and enhancements in 5.0, so let me talk about what I feel are some of the more important enhancements. This release provides a platform for users to take the best practices that are built in Alteryx and deploy them or deliver results to others in a more meaningful way. And when I’m say more meaningful, if I’m a business leader looking for an answer to a business problem, I don’t want to have to go through a big complex table of information to find the information that I’m looking for. I don’t want to get some complicated set of maps. I might just want an email attachment sent to me that has an embedded chart that says, ‘Here is your information.’ And so what we’ve done is we’ve created the Reporting Email Tool, so it’s really easy now to deliver the best practice in a metaphor that people are most accustomed to seeing. Rather than going to a separate place, I get it in my email, and it might be a scheduled process, so using some of the new scheduling capabilities in Alteryx, you can run multiple processes with one schedule. I can now send a bunch of different metaphors to a bunch of different people, either as email attachments or as output files that might be appropriate. And we’ve just made that process easier for people who are building that business-critical best practice, so that the deployment of it is much more efficient.

Ron: Was some of that thinking then behind the actual name change from Portfolio Scheduler to the SRC Scheduler?

Dean: Correct. And it was not just the name change, but it was some enhancements to it. I don’t know all of the detailed pieces of this. I try to keep up to date with the latest releases during the build cycles before a major launch like this, but I don’t use a lot of the tools myself on a daily basis, so it’s kind of hard to know. I look at the documentation to see what’s been accomplished. But we did change the name of the Scheduler, and we’ve added some other features because we heard from users saying, ‘Well, I have four processes that need to be run at the same time. Why do I have to establish four separate schedules if everything gets run on the same timeframe? So now you can invoke multiple modules with one scheduled process.

The other addition that I think is very useful is the Overlay Tool. It allows you in the reporting environment to take various snippets, map/text/image snippets, and place them anywhere on top of another snippet. So, if you needed to include text on top of a map to isolate something that is important in a map, or if you needed to have an inset in a larger map that highlights what’s happening very close to the ground, and you wanted it portrayed in the upper left-hand corner as your output, you can do that. So we’ve just made this metaphor creation and metaphor delivery easier with the Overlay Tool and the Reporting Email Tool.

Ron: I'm curious. As you go through and you list out all the new functionality, and all the new tools, when do you determine, ‘Hey, that’s enough, we have plenty packed into this release here, everything else is just going to have to wait until the next release.’ When do you make that call?

Dean: There’s a group of people within the company between the Products Management Team, Core Development and our Customer Advantage Group, who keep a very long list of feature enhancements. The feature enhancements generally come from two different sources: customers who have a particular requirement that doesn’t’ exist, or it exists in Alteryx today, but the process to get there is just arduous. An example of that is Batch Macros, where people who wanted to have an output feed another input instead of having to shell out and run scripts to invoke new macros. We heard this enough from a lot of clients, including you, Ron, so we decided that just having Batch Macros to automate inside of Alteryx makes the process a lot easier. Another example is related to an improvement in 5.0 that we made to the Formula Tool by including financial formulas, which make it possible to run financial analytics in addition to spatial analytics.

A lot of the things we add come from customers, and that list is long. We actually have, I guess there still are two or three dozen enhancements that won’t make it into 5.0. Perfect is the enemy of Good in the case of product releases. We could wait and put them all in, but we’re never going to be able to put them all in, because the list continues to grow every day.
I would say about half of the feature sets on the list of things that get added in the product and on the continuing list, half of them come from the customer and half of them come from our own internal customers, who use the product in very intense and varied ways.

For example, some of the things we’ve added this time, if you’ll notice in the macro palette, are similar to the features we added in the past: the Convex Hole Macro the Non-Overlapping Drive Time Macro. I think we added those just because they were somewhat unique, and they were far more efficient than traditional GIS environments. But we’ve added a whole bunch of new macros this time as well.

We’ve added macros such as the Count Records Macro. All it does is, it is a macro that counts how many records are going through the tool. And, as simple as it is, we thought, ‘That’s just a real common one; so just make it available to everybody.’ We’ve added a Date/Time Now Macro. This macro returns a single record: the date and time at the module run time and converts the value into the string format of the user’s choosing. Again, just for data governance, and things like that, people want to know the date and time that the module ran. So why have people create their own routine when we’ve already created it? And this is all getting towards the future of Alteryx and Alteryx Connect.

We added another new one for the macros: the Weighted Averages. This tool just calculates the weighted average for the incoming data field. Just something that is useful for downstream activity, for formulas and models and things like that. So the goal is to continue to build out best practices either in the form of new tools that do new things, improvements to existing tools that provide greater functionality or greater flexibility, or new macros that just button-up processes that people shouldn’t have to re-create on their own.

Another example of improvements—I think is a great improvement—I don’t know, Ron, if you have experimented with the spatial formulas in the Formula Editor?

Ron: Recently only with the SpatialInfo tool in 4.1, not in the beta.

Dean: Well we decided to migrate it to the Formula Tool, because the whole—we’ve been touting for a long time that spatial isn’t special. I know the GIS guys want people to believe that spatial is special—but the reality is a spatial formula shouldn’t be treated different from any other formula. In fact, we decided to migrate the spatial functions into the Formula Tool, because you shouldn’t have to drag another tool out if you are already in the tool in which it should natively occur.

I think in this release we’re adding six or seven additional macros that our own internal team uses. And we’re starting to realize, if we’ve built the best practice, why not just give it to customers, because they are just going to have to build similar processes on their own. And rather than having them do that, we want to provide an array of standardized best practices. If users decide to alter them on their own, they can open up the macro, make their own changes and save it the way they want.

To be continued…

Stay tuned for the next post that will continue my conversation with Dean about Alteryx Connect.

Till next time, enjoy!

Spider Maps Part Two: Revenge of the Araneae?

Today's post is a follow up to my last post dealing with Spider Maps. Previously I had posted a module that needed some tweeking, the primary issues being lines drawn to centroid rather than to the center and drawn as one polyline instead of many. It seems to me these are two of the primary reasons for using Spider Maps in the first place, so after posting and thinking about it a bit, I re-worked the module to resolve these concerns. Warning: this module is one of those "ain't pretty but works" modules - the workflow can be accomplished a multitude of ways, this is how I figured it out but if had the time would probably do it differently.

Anyway, this is the approach I took. It was fairly easy in the first module to find the centroid of all the market points and use that as the "hub". But playing around with the SpatialInfo Tool (which may very well be one of the most diverse tools in Alteryx - can't wait till 5.0 is released when much of the Spatial functions are available via the Formula Tool), I figured that I could roughly generate the lines for each market and create a "bounding box" around the lines, then simply use the SpatialInfo Tool again to find the centroid of that which is the converging center of all the points. Once I had that, then the process was no different than what I had done using the centroid.

To address the issues of all the "spider legs" being drawn as one, I had created a second RecordID, conveniently named RecordID2, to renumber the records after adding the duplicate records to accomodate for the consecutive line returning from the point back to the hub or center. These are the records that I replaced the original latitude and longitude values with those of the values found for the center of the "bounding box". Keeping in mind that the PolyBuild Tool needs the lines to be drawn in succession, and since I wanted individual lines, I grouped on this RecordID2 rather than market. This provided me with the initial and return leg of each line. The Summarize Tool was then used to group on RecordID2 Then I joined the distance data and original RecordID back up with the corresponding line.
Hopefully this makes sense, if not please reply to this post and ideally I can explain more. Here is the module for you to look at:
Download the module here!
Till next time, enjoy!

EEECK! SPIDERS!!!

Alright, so I'm a little late for Halloween, and today's post is not really about spiders, but it IS about creating simple Spider maps in Alteryx. This is a module I created to determine the centroid (not center - I did say simple!) of a series of points that represent a number of locations within a market. Market in this sense is really nothing more than an area of concern. You can use this module to create Spider Maps to represent how locations are related to each other within an area. Those locations may be customers, stores, facilities - really anything that defines the area's "extents".

Once the area or market centroid is determined, you can use the PolyBuild Tool to create the "spider" legs. The "trick" is to get the records in the right order since the PolyBuilt Tool needs a consecutive sequence in order to create the "spider leg" polylines. Due to this requirement, the end result is that all the lines are created as one polyline for each market. You'll need to revamp the module if you need to get individual lines, or "spider legs", yet I hope this module get you started in the right direction.

The process I used to create each "spider leg" was to first duplicate each point record in the list with the MultiRowFormula Tool, then replace the latitude and longitude values of the duplicate points with those of the market centroid. This creates two lines; one going out from the centroid to the point, and one returning back to the centroid, thus keeping a continuous sequence for the PolyBuild Tool. Essentially you are creating two lines for each "spider" leg, yet they are all combined into one once the overall process is complete.

To define the sequence of when the legs are drawn, I used the Distance Tool to determine the angle between the Centroid and the individual Market points and ranked the list on the angle found for each point.

The required Input data format for this module is Location ID, Market, Latitude, and Longitude. The Select and Join Tools can easily be modified to append any additional data you want to pass through to the downstream Output Tool in addition to the "required" input data.

Here is the module for you to use:
Click here to download the Spider Maps module
Till next time, enjoy!

Quick Text Tool Tip

Today's post is a simple, quick and easy Text Tool tip that I now use often while testing out new Alteryx modules. I saw this while attending an Alteryx training at Sprint here in Overland Park a few weeks ago given by Evan W., Margarita W., and Sinam A of SRC. Now typically I use the Text Tool for quickly adding data to a module when a data file does not exist, yet this tip comes in very handy when I only wish to test with a few records.

I learned that if a sample set of data is output via the Browse Tool, you can easily copy all records or a certain select set of records (with or without column headers) and paste them back into the module as a Text Tool containing the data. Once the data is copied onto the Windows Clipboard, just right-click a blank area in your module and choose Paste from the shortcut menu. A Text Tool is inserted for you with the data you selected. Pretty cool, eh?! Ok, ok, maybe I'm easily impressed, yet this typically saves me alot of time during testing and doesn't clutter up my hard drive with a number of sample files that I normally don't ever use again. I know, you're probably saying that you have to load the data into the Browse Tool in order to do this anyway, but if I test my module multiple times, the time saved is definitely worth the intial effort.

To illustrate this tip, let's run through an example where I've used it recently. I was testing a module and wanted to run a sample set of data out of a list of million+ records. I didn't want to have to filter or sample the entire list of records because I would then need to wait (with a million anything your going to have to wait) for the entire file to load each time I ran the module, and I also did not want to save this sample set into a file of whatever file format. So I opened a new blank module and filtered the data I wanted to use into a Browse Tool. After running this new workflow I had my sample set of data in a Browse window.

Then I chose the Copy to Clipboard icon on the Browse window to copy the data to the clipboard, shown at the left. There are two options to copying this data; Copy All Records or Selected Cells with or without Headers. Since I already filtered the data I wanted, and I want to use the same column headers with this data, I chose Selected Cells with Headers. After copying the data, I went back to my test module and pasted the data and voila! Now I have only the data I need to test within a Text Tool, I can modify it if needed, and I can test until my heart's content..., or at least until I finish testing this module.

Well, I hope this tip comes in handy for you and that you can use it as often as I do.

P.S. Here is a caveat to this post that I ran across on the Alteryx Forum, specifically in this post by Lonnie Y. This works well only if you are testing with a record set of less that 1000 records, which is the limit of the Text Tool.

Till next time, enjoy!

Alteryx FTP Get Wizard, or Conversations with Margarita

After a self imposed hiatus from the blog, I'm ready to get back at it again. And this post is all due to Margarita Wilshire, a friend of mine from her past life at Sprint and now new SRC Client Services rep. I was trying to figure out a way to transfer a file with FTP (File Transfer Process - the process for transferring files between two computers via the Internet) to another remote computer using Alteryx. This is possible with the RunCommand Tool and the FTP Put commend, which uploads the file to the FTP remote computer.
I spoke with Margarita about this on the phone and she provided me with a FTP Get Wizard she had just finished working on. Her wizard downloads all zipped files using FTP from a remote site and uncompresses them into a user specified working directory. And while I'm trying to upload files as compared to downloading them, the process is very similiar, so I've made her wizard available for download below.
Download the FTP Get Wizard here!
So let's take a look at the process that her wizard follows, but first lets review a bit about how the FTP service works. The Windows FTP service has a series of commands and parameters which run within the Windows command interpreter (cmd.exe). The FTP service also allows placing all of the commands to run at one time within a file, which Margarita called ftpcsv.csv in this wizard. The first two records in this csv file are actually placeholders for the user's username and password to log on the FTP server, so make certain you replace the username and password values with your username and password information.

The commands she used are listed below:

binary - in this particular use of the wizard, we are downloading all (*.zip) zip files. Since these are not text based files, we need to set the transfer type to work with binary files.

prompt - we will be downloading multiple files, so we need to toggle off the ftp file prompts.

mget *.zip - this is the command to retrieve multiple files from the remote computer using the "*" (asterix) wildcard character to download all files with a zip extension.

quit - ends the ftp session.

exit - closes the Windows command interpreter.

So the first step is to use the Text Tool to place the commands we want to run, along with our username and password to access the remote ftp computer, into a csv file called ftpcsv.

Next, the Alteryx RunCommand Tool executes the ftp service with the commands supplied in the ftpcsv file, which establishes the ftp connection to the remote computer. The FTP Get command downloads all zipped files within the default directory using the 7-Zip software available at www.7-zip.org. This open source software uncompresses a number of compressed formats, including zip files, thus the reason for using it in this wizard - no licensing issues or proprietary software to deal with. The third step then uses the RunCommand Tool to unzip the downloaded files. In order to do this, the 7-Zip program requires a temp file called temp.csv in order to run, so the Text Tool is used to create the data for the file (simply a record with a value of "Temp") and save the file with the Output Tool.
One note about running the FTP Get Wizard; you are prompted to choose a folder that defines the working directory. This is the directory that the zipped files will be downloaded into and where they will be uncompressed. It is also the directory that the wizard looks for the ftpcsv.csv file, which contains the FTP commands to run. Ths first time you run this wizard you must copy the ftpcsv.csv file included in the wizard download above to the directory you specify to be the working directory. If not, the wizard will not work because it will not be able to find this file.

Please post a comment if you were able to get this to work or with any other questions you might have.

Till next time, enjoy!

The Calgary Cross Count Append Tool

One of the things I like most about the Calgary Tools is that once a Calgary database is created I can easily apply it to a number of inquiries. Case in point, customer data is always a good example of a data set that is queried repeatedly in multiple ways. Granted, you have your standardized reports that are run regularly on this data, yet it is the ad-hoc requests that make working with the data so interesting. Maybe you know the type of requests I'm referring to; like how many customers are nearest a set of stores in a market excluding factors A, B, C, and D. It is sometimes amazing to me the numerous ways customers want to see the data applied, and thankfully software like Alteryx and the Calgary Tools make much of this possible. Ok, so much for sounding like an ad for Alteryx, let's get on to the primary focus of this post.

If there is a boundary that you want to determine the numbers of customer within, the Calgary CrossCountAppend Tool provides the easiest method for doing so. As an example, I've modified the Polygon Defined By Points module and added a Calgary CrossCountAppend Tool to determine the number of customers that fall within a polygon created using the Pet Store.tab sample data file that ships with Alteryx. For a customer file, I generated a random sets of 184,000 plus points that represent customer locations near these stores saved as a Calgary database. Once the customer points are saved in this format, it's a rather quick process to determine the number of customers within any boundary using the Calgary CrossCountAppend Tool.The key to getting this tool configured is setting the Join Criteria so that the Centroid of your Calgary database file is joined to that of the boundary file, as shown below for this example.Then you can specify which fields you want count data associated with within the Cross Count Field tab, depending on what data you have stored within the Calgary database. For example, if you have segmentation data or prime\sub-prime financial or purchasing data saved in the database, you can obtain count information based aggregated by these customer types on those customer location points that fell within the boundary. To obtain the count information displayed in the table as in this example, the RecordID was used as the only Cross Count Field.
Download the updated module with data here!
This is only a portion of what can be accomplished with the Calgary Cross Count Append Tool, yet this provides a quick method for obtaining customer count information within any boundary.

Till next time, enjoy!

Upgrading to Alteryx 5.0?

As it is getting close to the release of Alteryx 5.0 (4th Qtr 2009), I'm curious to see how many users plan to upgrade to the latest version? Whether you are or not, please take a second to fill out the poll to the right. Don't worry, the poll is anonymous and strickly for the benefit of the blog. The reason for this is I'd like to focus on new functionality and tools in Alteryx 5.0 for the month on October on this blog. Yet I only wish to do so if it will benefit the majority of the blog's followers and community. So if you find this blog useful, participate in the poll.

Also, I'm interested in hearing from you what you would like posted over the remaining months of 2009 (yes, already we are on the downhill side of 2009!) So I'm all ears... and nose... and ah... scalp! =(;-) To suggest an idea on the workings of a specific tool or module, reply to this post with your suggestions and I will strive to incorporate them into the blog in the upcoming months.

Till next time, enjoy!