The a**3 Blog (all about Alteryx)

Easily Integrating Census Data into your Alteryx Workflow With the Census Data API

2018-05-16T07:03:00.003-05:00

I often work on Code for KC (America) projects where there is the need to incorporate census demographic data into my Alteryx workflow beyond what is available within the standard Data license. Typically I'll work with the current American Community Survey (ACS) data only and other times I'll need to compare the most recent ACS estimate to older census data or utilize some of the Census Comparison profiles. In each of these instances, I've found using the Census Data API to be invaluable because it is so easy to include data using APIs within Alteryx. I've used this recently to pull certain demographic variables into a workflow on an Abandoned to Vibrant Properties project conducted in conjunction with the University of Missouri-Kansas City (UMKC) and the Land Bank of Kansas City. This work was submitted as an Alteryx Excellence Award and you can read about (and ideally vote for!) it here: a2V-Lb: Abandoned to Vibrant Land Bank Housing in Kansas City, Missouri

This post will walk you through working with the Census Data API and how to create a reusable snippet to streamline the process of bringing the data into your Alteryx workflow. As with any API, understanding how to request the data and how to utilize the incoming data in your workflow is key. So let's learn a bit about the API and how to format the URL to request the data you want. In this example, we'll focus only on using the API created for variables accessed through 2016 ACS Detail tables. In addition to this dataset, there are a variety of other census datasets that can be accessed:

Detail tables contain the most detailed data down to the block group level in population counts. You can view variables and examples of the detail tables and supported geometry types here.
Subject tables provide an overview of the estimates on a variety of subjects. You can view the subject tables variables and examples here.
Data profiles are presented as population counts and percentages and provide access to a wide variety of social, economic, housing, and demographic information. Examples of these profiles and variables, examples and supported geometry types are listed here.
Comparison profiles contain similar data as the Data Profiles above yet also include a current year comparison to the prior five year data along with statistical significance. View the available variables, examples and supported geometry types of the Comparison profiles here.

An example is shown below of a complete, encoded Data Profile Census Data API URL. This example returns the total population count for each block group within Johnson County, Kansas:

https://api.census.gov/data/2016/acs/acs5?get=NAME,B01001_001E&for=block%20group:*&in=state:20%20county:091&key=YOUR_KEY_GOES_HERE

You can find other common examples here.

The Breakdown of Query Components

To better understand what goes into the definition of the query URL, let's break it down into two distinct components:

The API Base URL - The base portion of the API URL defines the census year and the dataset you wish to access. To obtain data from each of these datasets, you will use a unique base API URL (Note: The base URL changed in 2016, check here for previous year examples):
- Detail tables:
```
https://api.census.gov/data/2016/acs/acs5?
```
- Subject tables:
```
https://api.census.gov/data/2016/acs/acs5/subject?
```
- Data profiles:
```
https://api.census.gov/data/2016/acs/acs5/profile?
```
- Comparison profiles:
```
https://api.census.gov/data/2016/acs/acs5/cprofile?
```
The GET function - This function contains the required census variables you are requesting and starts with get=. This function also includes the predicate, which identifies how the census data will be filtered or limited. Predicates can be created using geometry, string or numerical variables, or by time if accessing the time series datasets. In the example above, this will request the census data for a specific state and county identified by their corresponding Federal Information Process Standard Code, or FIPS Codes.
- Geometry Filters - The level of geometry that can be used within the predicate to filter the data varies for each dataset. For example, you can request data at the block group level using the for= statement: (&for=block group:*&in=state:20 county:091). The wildcard (:*) can only be used to search for all values of a variable for geographies and string variables. You can view the available geographies for each dataset by selecting on the corresponding link below:

The Census API Key - Along with each request you'll also need to provide an API key that identifies you as the requestor, which you can submit for here. This must be included as a part of your data request URL string.

Working with the Census Data API Snippet

Now that we know how to request the data, let's take a look at the snippet (shown in the image above) that we'll use to request and download the data. This was specifically designed to handle variables requested from the 2016 ACS Detail table. Depending on the data you request and the level of geometry desired, you will need to modify the snippet accordingly. At a birds-eye view, these are the basic steps used to access any of the census datasets with this snippet:

Define the FIPS Code(s) associated with the geography you wish to filter the census data with the Filter Tool.
Using the Formula Tool, create the query definition with the desired census variables and predicate. This will dynamically create the API endpoint based on the selected FIPS Codes. Remember that you can modify the for= statement with a wildcard (:*) in the predicate (for=block group:*) to return variables for all block groups within the chosen geography. In this snippet, the State and County FIPS is used. Choose multiple State FIPS Codes in step 1 if you wish to pull the variables for all Counties and block groups within those States.
Use the Download Tool to submit the request.
The JSON Parse Tool parses the result, and the Text to Column Tool splits the JSON_Name column into two columns.
The stream is split into Header and Data streams using the Sample Tool, based on the level of geometry desired and number of variables requested defined in the predicate.
After formatting the two streams, join them back together using the Join Tool.
Rename fields from variable names to more descriptive common names with the Select Tool.

Running the Census Data API Snippet

To run the snippet follow these steps:

Select the State FIPS Code(s) you wish to pull data for in the FIPS Code Filter.
Choose the variables you want to obtain. The available variables for the Detail tables can be viewed via this link. Each variable must be separated by a comma and I recommend to keep your variable list to less than 10. Start with just a few variables at first then add more with each subsequent run. This is an example of what your predicate should look like with 5 variables in the URL Creator Formula Tool within the snippet:
```
get=B01003_001E,B00002_001E,B19013_001E,B23025_004E,C17002_008E&for=block+group:*&in=state:" + [FIPS State] + "+county:" + [FIPS County]
```
Submit for a Census API key and substitute it for the words "YOUR_KEY_GOES_HERE".
Now you're ready to go! Run the snippet.

I've packaged the snippet for your use, see attached. Feel free to modify it as you see fit and remember to share your modifications with the Alteryx Community. If you like, you can fork the snippet from my Github Repository.

Enjoy!

Ron

And Now in Your Spare Time...

Want to test your knowledge of the Census Data API pulling other data variables? Modify the snippet to work with these other examples:

Comparison Profile Example - 2007-2011 School Enrollment of Population 3 years and over enrolled in High school (grades 9-12) by MSA:

https://api.census.gov/data/2016/acs/acs5/cprofile?get=CP02_2007_2011_056E,NAME&for=metropolitan%20statistical%20area/micropolitan%20statistical%20area:*

Data Profile Example: 2016 Housing Occupancy, Total Housing Units for all census tracts in the State of California:

https://api.census.gov/data/2016/acs/acs5/profile?get=DP04_0001E,NAME&for=tract:*&in=state:06

Subject Table Example: 2016 Female Total Population Age 60 to 64 by Zip code Tabulation Area:

https://api.census.gov/data/2016/acs/acs5/subject?get=NAME,S0101_C03_014E&for=zip%20code%20tabulation%20area:*

Note 1: You can determine census variables for other geography types by using the Census Relationship files found here.

Note 2: Although I have not tried it yet, you can also access the census data via the Census Data API using walkerke's tidycensus R package found here (using the R Tool).

Alteryx for Good - Kansas City's Land Bank Side Lot Program

2016-06-13T05:47:00.000-05:00

At Code for Kansas City, occasionally we are asked by community stakeholders to take on projects that we would not consider as a typical software development project. Usually, these are one-time projects where it doesn't really make sense to develop a a unique piece of software or justify the creation of a website to complete the requested task. This is where the use of Alteryx via the Alteryx for Good program is uniquely suited to provide a solution. One such project is the Kansas City Missouri's Land Bank Side Lot Program. The KCMO Land Bank, which focuses on returning vacant properties to productive use, asked the Code for KC Brigade to assist with the promotion of a program where they have hundred's of smaller "side lots" to offer. These side lots are 6,500 square feet or less and many sell for as little as $1! To qualify one need to be adjacent to one of these small lots (among other criteria), which are perfect for a garden plot, a swing set play area, or if you just want a bit more "breathing room" to add to your property.

When the Land Bank first came to us with this project, we debated the best way to promote this program. Sure, we could have easily created a website displaying all of the side lots, yet we knew that then we would need to promote the use of the website in addition to the program. Since this program is only available to those property owners that are adjacent to the side lots, it wasn't really the ideal solution. In the end we and the Land Bank agreed that it would be best to send out flyers in the mail for each of the adjacent properties informing the property owners of the program. Yet we also realized that it would mean hundred's if not thousand's of flyers would need to be created to encompass each adjacent property. No small task, yet I knew this would be a fairly easy one to handle using Alteryx.

First I needed to get the data, and luckily I was able to use the Kansas City Open Data Portal to pull the Land Bank property list. Using the Download Tool I was able to leverage Socrata (which was used to create the KC Open Data Portal) and its API to bring the data into my flow and join it with a shape file of the parcel data I obtained using the KCMO Parcel Viewer. I filtered the list to find all the properties under 6.500 square feet in size and those which did not have a "sold" date and found all the adjacent properties which touched the side lot boundaries with the Spatial Match Tool. Once I knew the adjacent properties, I used the Reporting Tools to create each map and format the flyer and output it as a PDF file. I also adjusted the position of the mailing address for the Adjacent Property Owner so it would position correctly into a windowed envelope when the flyer was folded. Below is an example of one of the flyers:

This represents just one of the many ways we are able to use Alteryx through the Alteryx for Good program to do good in the Kansas City metropolitan area, so check back often on this blog for future posts on how Code for KC uses Alteryx for the greater good of the community.

In my next post I'll discuss how to bring open and census data into Alteryx using the Socrata and CitySDK API's similar this module.

Till next time, enjoy!

Ron

The Alteryx Analytics Excellence Awards

2016-03-10T08:29:00.000-06:00

Hey Alteryx Community! How do you give back to your community or to that special cause? I enthusiastically tag a post from the Alteryx Community blog stating that Alteryx has created a competition where Alteryx users can give back by using Alteryx to benefit deserving charitable organizations! The Alteryx Analytics Excellence Awards was kicked off yesterday, and the winners will split $100,000 to give to a number of great charities. There are a variety of ways you can participate and and submit stories to win in one of 8 categories. The contest started on March 9th - don't delay and submit your stories here.

This is a part of the Alteryx for Good program, which was first announced by Dean Stoecker on LinkedIn a few weeks back, the #alteryxforgood program enables the Alteryx Community to become a part of something bigger than themselves.

This is a contest that is a big WIN for all involved, so think about it. How can you give contribute your time and talent to give back to a good cause that means a lot to you?

Alteryx, Code for KC and Giving Back in Kansas City

2016-03-07T12:18:00.000-06:00

Today the White House announced the Open Data Opportunity Project, a new open data effort to put data and tools in the hands of local communities, families, individuals and businesses to help them navigate resources to access transportation, jobs, housing, schools and other neighborhood amenities. Also as a part of this project, they are also launching a Opportunity.Census.gov website, which provides links to numerous open data sets and tools that leverage this data and more.

This is fairly significant because one of the main issues typically faced at any Code for America (CfA) Brigade when working on community projects is finding, normalizing and maintaining the data we'll use. This is no different at Code for KC, the Code for America Kansas City Brigade, the method I choose to give back to my community by donating my time and talent. And since a considerable amount of time can be given to data collection and maintenance effort, this can often increase our project time significantly. Therefore, I've proposed to the Code for KC Brigade that we start to utilize Alteryx to help blend and maintain the various data sets for our projects to give us a "quick start" and to minimize the overall project life cycle. And since our projects are normally created using open source software and are designed to be handed off once the project is completed, Alteryx will also be used to document which data sets and API's are used, joined and propagated for the different projects prior to hand off.

In the next few blog posts I'll document how the Code for KC Brigade leveraged Alteryx to assist on different projects and illustrate how it decreased our project beginning to end time frames.

Check this blog often for upcoming posts concerning this effort! Till next time...

Alteryx and github - A Place for Sharing Great Ideas (and Macros)?!

2015-02-26T10:29:00.002-06:00

Have you you ever downloaded a macro from the Alteryx Gallery and then upon examining how the macro was created you wonder why it was designed that way? And then if you wanted to get feedback on changes you made to a macro, you could request comments by submitting it to the Alteryx Forum, yet I'm wondering if there is a better way.

Many of us in the development community have used github as a repository to host new and upcoming projects for others in the community to learn, expand and improve upon. So I started thinking... What if there were an Alteryx github repository (or something similar) where users could upload new macro projects, enabling others to fork (github "speak" for duplicating a repository) these projects and contribute enhancements to the macros? Then as the community evolves the macros and they mature into a final version, they get uploaded to the Gallery (maybe this is part of the Gallery itself).

I do not claim to be the best at creating macros, yet in proposing this I like to you, have an intense desire to learn how to become a better Alteryx user and macro designer (else you wouldn't be reading this blog!). What better way than to learn from the Alteryx community beyond the great tools Alteryx has already provided?

I'm certain that the smart folks at Alteryx have already considered something like this, and what I'm proposing is surely not new. Yet I'm submitting this concept to the Alteryx community as a whole to garner some friendly and constructive conversation around this. And as an example, I've packaged up the macro from my last post and placed it on github here. Feel free to fork and suggest changes. For those of you unfamiliar with github, here is a great video on YouTube on how to get started.

Now I'm asking the Alteryx community to speak up on this - regardless your Alteryx skill level! I know you have a voice, so let's hear it! What are your thoughts on this?

Till next time, Enjoy!

Ron

Creating Worldwide Distance Matrices Within Alteryx Using the Graphhopper API

2015-02-23T13:35:00.000-06:00

I'm always searching for new and exciting API's, and this week I came across one called Graphhopper. This relatively new (beta for now) service describes itself as a fast and flexible car, biking and walking route planner that offers world-wide Routing, Geocoding and Matrix API's using OpenStreetMap data. A further look into the documentation on Git, I found that Graphhopper also provides a mapping service too by combining the Routing and Geocode API's.

In this post we'll use the Graphhopper Matrix API to create a relatively straight forward macro that generates a 5 x 5 matrix report snippet and outputs it as a pdf file. For those of you not familiar with a Distance Matrices, below is an example using a series of random points. This particular matrix example lists the distances between each of the points.

The Matrix API also generates Time and Weight matrices as well (think of weight as the amount of effort to move between one location and another; typically used in Vehicle Routing Problems, or VRP's). Granted, there are a number of ways to improve to output from this sample macro, yet let's talk a few possibilities later in this post. And since we are using a Graphhopper developer key to create the matrix, we are capped to a maximum of five points (you will need one to run the macro - more details here) and limited to 100 matrices a day.

Here's the approach used to create this macro:

Use the Interface Tools to create the macro interface that pieces together the Matrix API request with user-defined values to the required parameters.
Use the Download Tool to pass the request to Graphhopper, which returns the result as JSON.
Parse the resulting JSON with the JSON Parse Tool, outputting the values into data type specific fields.
Filtered out the null values, converting the remaining values into miles (by default they are in meters) and pass them to the MakeColumns Tool.
Generate a basic table with the values using a Table Tool and rendered the result as a pdf file with the Render Tool.

Here is a screenshot of the finished macro with notes indicating what each tool does:

I plan on making this available by submitting to the Alteryx Gallery. If you would like a copy prior, just respond to this post and I will send you a copy in the interim. Also, here are some thoughts on how to further improve the macro:

Add the option to choose between distance, time and \ or weight matrices and output all three.
Leverage the Graphhopper Geocoding API by first geocoding the points to be used in the matrices.
Output a map displaying matrix routes in addition to the matrix.

I'm curious to hear your thoughts on how to add to the macro functionality. So if you do not have access to Network Analyst, pgRouting or other routing software or are looking for an alternative, I definitely suggest you check out the Graphhopper services and API's!

Till next time, enjoy!

Ron

Google Fusion Tables, Alteryx and oh yes... Godzilla!

2012-01-26T00:29:00.000-06:00

So you're probably wondering what does Godzilla, Google Fusion Tables (GFT) and Alteryx have in common? Well, GFT and Alteryx have more in common than Godzilla does with either, but just as I stumbled across a Godzilla movie on TV I figured how to pull data from GFT into Alteryx, so I thought it was only appropriate that I give it credit by placing his\her image on this post.

=(;-)))

Seriously though, if you haven't done much with storing data in the 'cloud', working with GFT is a great and painless way to get comfortable with the process. To get started here are two of my favorite tutorials that cover how to load and edit data placed in GFT, to geocode it and how to create charts, maps, (and heatmaps!) with your data.

The first is Google's own Google Fusion Table Tutorials and the second is a YouTube video by Circle of Blue.

And now back to our regular programming...
Ok, so how do you bring GFT data into Alteryx? The process is rather straight forward yet the formatting is essential - more on that later. Data loaded into GFT can easily be manipulated via the Google Fusion Tables API and one of my favorite utilities, cURL, along with the Run Command Tool. Let's look at this example pointing to a sample table I created of road locations where certain Vermont vehicles were speeding at a particular point in time. If you select the link below a csv file is downloaded containing all records (thus the *) from this GFT which has a numeric identifier of 2745825:

http://www.google.com/fusiontables/api/query?sql=SELECT+*+FROM+2745825"

The API URL request is simply made up of 'http://www.google.com/fusiontables/api/query?sql=' followed by the SQL query you want to run against the data loaded in the GFT identified by its Numeric ID. Here is a slightly more complex example using a SQL WHERE clause:

http://www.google.com/fusiontables/api/query?sql=SELECT+*+FROM+2745825+WHERE+OverLimit+=+'27'

This will request all records where vehicles were travelling greater than 27 miles over the speed limit in this dataset. Configuring the Run Command Tool Properties with cURL to access this data looks like this:

Also remember that I mentioned format earlier? You must remind yourself that the API URL request you use be encoded. This means that the any characters within the URL must be in a format that can be transmitted over the Internet, which explains the 'plus' signs instead of spaces in the SQL query - here's a quick reference:

w3school's URL Encoding Reference Page

If you do not encode the API URL the download will not work. Just replace the desired SQL query shown in the example above with your own and don't forget to reference your GFT Numeric ID. Now you might be wondering how to find the Numeric ID. To do so you must open your GFT, then go to the File pull-down menu and choose About to view the properties for that table (see image below).

You must make certain that the Tables Visibility you are wanting to access is either set to Public or Unlisted. If it is set to Private, you must first authenticate with a username and password to get to the data. Discussing this is a bit more than I wish to cover in this post, yet I will point you to a good reference by Pimin Konstantin Kefaloukos (he goes by Kostas) who has a blog called skipperkongen.dk:

Google Fusion Tables Cheat Sheet

To give you an idea of what can be done with the data you upload into GFT, here is my original file displaying a larger set of Vermont vehicles based on miles over the speed limit as a Heatmap.

Till next time - Enjoy!
Ron @ All About GIS

Leveraging Location-Based API's Within Alteryx 7.0

2011-12-27T14:31:00.000-06:00

Happy New Year Alteryx Users!

In my last post I introduced a Reverse Geocoding Macro that returned address information using the new Parse XML Tool in Alteryx 7.0 and the Google Maps API for Geocoding. Using the XML Parse Tool, you can extract the returned XML data from the API, giving users access to a whole host of data never before available within Alteryx. With the Google Maps API for Geocoding being only one of the many API's that return data in XML format, I wanted to blog about the various location-based API's you might be able to leverage when the 7.0 version of Alteryx is released next year.

Yet as I was gathering this info, I ran across a post by Kin Lane on his blog that summed up nicely what I wanted to say, so not to dupe what he had already written, I will provide a link to his post here:

Quick Walk Through World of Location & Places API's

Not to say that all of these API's return data in XML format for you to use, yet I hope it does start you thinking about the multitude of API's, the data that can be extracted from them and what you can do with it now that you have access. That said, you also must diligently dig through the data and see if it is appropriate for what you wish to use it for. Following the age old saying "A map is only as good as the data it represents"; be careful of what is being returned, as Whit Nelson found out and posted about here:

Why you shouldn't use the Yelp API

Wishing all of you a great New Year and please keep All About GIS in mind for your Alteryx training needs in 2012!

Till next time, enjoy!

Ron @ All About GIS.net

Bulk Reverse Geocoding Macro for Alteryx 7.0 Beta!

2011-12-11T21:21:00.003-06:00

Happy Holidays Alteryx Users!

Can I see a show of hands of those who are beta testing Alteryx 7.0?

If you are, then you already know about the great new features coming up in the next release. If not, check out these posts by Adam Riley (Chaos Reigns Within), Ned Harding, Linda Thompson and Rob McFadzean among others at the Alteryx Engine Works Blog. One post in particular by Ned discusses the new Parse XML Tool, which enables you to parse through an XML file and extract the data. This tool by itself opens up a whole host of possibilities accessing the numerous "free" API's (Application Programming Interfaces) available that pass back data in XML format - specifically some of the Google Map API Web Services used to create the macro I'll describe in this post.

http://geolocator.allaboutgis.net/

In this instance, using the Google Map Geocoding API, I was able to create a Bulk Reverse Geocoding Macro in the Beta release. Kudos go out to Cameron Steele of Intalytics and Jed Klink of McDonald's who provided much input on the end result, and thanks to Brett Gottdener at Alteryx on getting the batch macro configured correctly. It all started one day when Cameron and I were brainstorming about a concept. Working with the API, HTML 5.0 and JavaScript led to the creation of a world-wide Geocoding \ Reverse Geocoding Mashup called "Geolocator", of which you can view at this URL (image at right).

The GeoLocator Mashup provides basic geocoding functionality if you enter an address, or if you pick a point on the map, returns an address corresponding to that point (or reverse geocoding). But you need to know where you want to pick on the map to obtain your result. In talking with Jed about this mashup, which can also export the results of the geocode \ reverse geocode process as a csv file using a bit of jQuery, he wondered if it could do bulk reverse geocoding. Still working on a mashup for that, but it got me thinking about some of the new tools in the Alteryx beta. Sooo, back to the macro and how it functions...

To make the macro work, you input a list of Latitude and Longitude coordinate values. These are passed to the Google Maps Geocode Web Service using the Run Command Tool and cURL, which returns the results for each location. The results are passed to the Parse XML Tool, and for simplicity sake, I configured the tool to only parse for a specific Child element (formatted_address), or the first address returned per coordinate pair. Depending on the accuracy of the coordinates you pass and what country they are in typically defines the level of accuracy. To see a sample of what the Google Map Geocoding Web Service returns, click this link:

http://maps.google.com/maps/api/geocode/xml?latlng=38.976302,-94.665756&sensor=false&callback=parseme

To format the data after it is parsed, I used the Formula and Cross Tab Tools. Parsing the formatted_address element returns Address, Neighborhood, City, County, State, Zip Code, and Country for each record, if available.

One caveat though - use of the Google Map Geocoding API is limited to 2,500 geolocation requests per day, with Premier users being limited to 100,000 requests a day. If you exceed these amounts you will receive a "Request Denied" message instead of an address, so keep track of your record counts. Note that this macro was made primarily for the United States. The formatting will need to be modified for use on other countries.

Once Alteryx 7.0 is released, I'll provide the macro to Alteryx for posting on the Module Exchange for your use. If you are an Alteryx 7.0 Beta tester, let me know and I'll send the macro your way for further testing.

Shameless plug coming up! All About GIS is now offering Alteryx Training! We offer both distance learning as well as onsite training, for beginning users to power users. E-mail me for more details and book your training today!

Till next time, enjoy!
Ron @ allaboutgis.net

Don't Miss Out!

2011-11-14T10:06:00.002-06:00

Hi Alteryx Users!

If you haven't subscribed to the All About GIS Alteryx 'Altryx' & Tips newsletter, you're missing out on helpful hints on how to use various Alteryx tools delivered right to your Inbox.

Since my last post I decided to change up the format a bit, offering detailed information about different tools rather than just tips and tricks. I've started offering the newsletter as a way to reserve the blog for more detailed projects, such as more complex Alteryx Modules, Macros and Wizards. Using the newsletters format provides me with a way to quickly provide tricks and tips in a faster, shorter format. To the right are images of the first two issues.

I'll still be posting via the blog, it's just that the posts may be spread out a bit. So if your interested, just send me your e-mail and I'll add you to the list. With each and every newsletter, you have the option to unsubscribe, making it easy for you to opt out whenever you choose. You can view the last two issues here:

Issue #1
http://alteryxnewsletter.allaboutgis.net/Issue1.html

Issue #2
http://alteryxnewsletter.allaboutgis.net/Issue2.html

Till next time, enjoy!

Ron

BTW, don't forget to check out Adam Riley's (previous UK Alteryx User Blog author) updated blog about Alteryx, Chaos Reigns Within!

The Quick and Easy Way to Learn More about Alteryx!

2011-10-19T19:41:00.003-05:00

Happy Halloween Fellow Alteryx Users!

I'm finding that with each release of Alteryx there seems to be more that can be accomplished with the software, and along with that comes more opportunities to learn about the product.

You might have recently noticed an addition to the upper right hand corner of the blog. It's a button to subscribe to the All About GIS "Al-Tryx" & Tips e-mail newsletter. Dedicated to the use of Alteryx and its users, this once a week e-mail newsletter is a short and to the point offering that covers one or two Alteryx tricks or tips in brief format. This is one of the best ways to quickly provide you little "snippets" of valuable free information on using Alteryx.

I'm using Google Connect to allow users to sign in using an existing Google, Yahoo, or Twitter account, making it very convenient for you to subscribe.

Once you are signed in, you'll see this text to the right in place of the Subscribe button, allowing you to easily make changes if needed.

Here are some examples of what to expect in the newsletter:

When working with the Spatial Match Tool and you have two polygon files as inputs, place the larger polygon file as the Target (left) input. When working with points and a polygon file, the polygon file should always be the Target input.

Did you know that the Browse Tool Table view can display up to 2 billion records? If you have more than 2 billion records in your data stream, all the records will be written to a file when exported using this Tool even though the remaining records are not displayed.

There are five methods to set a custom projection with the Output Tool Edit Projection option. They are listed below:

Well Known Name, such as WGS84 (the default in Alteryx) or NAD83.
EPSG (European Petroleum Survey Group) Geodetic Parameter Dataset ID's, like EPSG:27700.
Well Known Text (WKT), not to be confused with #1 above, and can be defined by either OpenGIS or ESRI dialects.
Web Map Service (WMS) auto projection.
Cartographic Projects Library coordinate system definition (PROJ.4).

AND If you haven't already, I encourage you to download and PUT TO USE many of the tips and tricks in the Alteryx tips-and-tricks-2011.pdf available from the Alteryx website here. These tips will save you a considerable amount of time and effort and it is well worth reading.

So if you wish to learn more about Alteryx with minimal effort, subscribe to the All About GIS "Al-Tryx" and Tips E-Mail Newsletter!

Till next time, enjoy!

Ron House
All About GIS, LLC
http://allaboutgis.net

Viewing Data Points in Google Maps & Streetview

2011-10-02T22:30:00.008-05:00

Hello All,

Well, its finally time for me to focus fully on my company, All About GIS, LLC! As a Geospatial and Business Intelligence service provider, All About GIS offers GIS and Alteryx professional services and training to the Retail and Telecommunication industries. Nuff said - please wish me well on my new endeavor!

Now for the main topic of this post. Have you ever wished you could view your Alteryx data points in a dynamic Google Map instead of the static images currently provided by the Map Tool? Then check out my Google Streetview and Map Viewer (GSMV) macro! If you have a spatial data point file, run the data through the GSMV Macro to output a local .html file, similar to the one at right.

There is only one question you need to answer in the macro, and that is where you wish the html output file to be placed. A select list is created of your data points to the left of the map. Choose a location from the list to place a corresponding numbered map icon on the map. You'll need Internet access to zoom in and out, pan, and toggle between the standard Map and Satellite Google Map backgrounds.

Dragging the Google Pegman onto the map fills the map window with a Streetview panorama of the current location. Once you are finished navigating the surrounding streets, you can close the panorama window which returns you back to the map window. In order for the macro to run you are required to supply the Address, City, State, Latitude, Longitude and Centroid of each location in your input.

You are limited to the number displayed on one page to 100 locations, which is the maximum number of points to efficiently view on one page.

I've documented how I created the macro (see above, pardon the artistic liberties I've taken) predominantly using the htmlpassthrough tag while using the Text Tool's Expert Mode. You can reference previous posts by me (Generating Interactive Web Pages Using Html, i.e. The Cuppa Joe Finder module) and on the UK Alteryxuser blog by Adam Riley (Street View Macro). The main difference between these Alteryx applications and this one is that multiple markers can be placed on the Google map that represent your locations of interest. Here are a few suggestions as to what you might use this macro for:

verify geocoded points
check out what, or who, is nearest to your locations
view the visibility your location has, or will have from the street.

I'm hoping to post this macro on the Alteryx Module Exchange, yet if you wish to get it sooner, send me an e-mail titled "GSMV" and I'll reply with the macro. Note: This has only been tested with Internet Explorer 8.0 and higher.

Till next time, enjoy!

Ron

http://allaboutgis.net/

Map Theming With Same Color Gradients

2011-02-21T07:58:00.004-06:00

Hi fellow Alteryx Users!

Today's post is a simple post, yet I wanted to quickly share something with you that might come in handy. I typically generate a number of maps with Alteryx using the Map Tool, yet I find that the palletes available for theming are a bit lacking, especially with same color gradient palettes, like the one used to create the map displayed on the left. No problem, it is extremely easy to modify and create your own palettes, and a number of topics have already been posted that describe how to do this. Check out these posts in the Alteryx forum:

Margarita Wilshire's - Adding my own palette...

Angela Ogle's - Adding Map\Chart Color Palettes...

or Adam Riley's Palette Importer and Exporter

I will not repeat what the posters have already done so well, but I do wish to provide you my ReportSettings.xml in hopes that it might save you some time and provide an example of what is possible. Here is a screen shot of some of the additional palettes that have been added:

Download My ReportSettings.XML File here...

Enjoy,

Ron

Slick Alteryx API Enabled Windows Azure DataMarket Mobile Retail Site Locator

2010-12-23T21:04:00.007-06:00

Happy Holidays, Everyone!

Well it's been awhile since I've posted last - there has been a lot going on personally as well with Alteryx as Alteryx 2011 (renamed from 6.0) has now been released. Just recently I met with a few good friends over lunch who also happen to be Alteryx users. After catching up, the discussion quickly focused on new Alteryx offerings (Geek Alert!). I remember seeing a video recently about a Mobile Retail Store Locator created using the Alteryx API and data. Since they had said they had not seen it, I thought it would be good to post, or re-post this video, first seen on the Alteryx Website. So, for those of you that have overlooked it, here's the video from the Microsoft Professional Developers Conference:

Although the video primarily focuses on Alteryx data offered up via the Windows Azure Datamarket, it demonstrates the flexibility and power using the Alteryx API in a way I have not seen before. This definitely opens up a completely new realm from which to leverage Alteryx.

Wishing you all a great Holiday Season, I will be back next year with more interesting Alteryx topics!

Till next time, Enjoy!

Ron

Area 51 - Calling All Alteryx Users!

2010-08-12T06:14:00.010-05:00

As I was strolling around on the web the other day, I ran across a very cool site called the Stack Exchange Area 51 Staging Zone. Similiar to StackOverflow.com, which is a collaboratively edited question and answer site for programmers; Area 51 - where groups of subject matter experts can band together to create free Q&A sites of any topic of their choosing, appealed to me. And so the first thing I searched for was Alteryx - hmmm, no sites found. Intrigued by this, I dug deeper to figure out how to launch a new site. To begin a proposed Area 51 site, a site must initially go through a specific lifecycle containing these four stages:

Follow it - The site must first show that enough users are interested in the site topic to follow it. A total of 60 users must follow the site
Help Design the Community - The users must then submit good questions that can be rated on-topic or off-topic. Each site must have both types of questions, because the off-topic questions are just as important as the on-topic ones. Once a question recieves 20 votes, and has at least 4 votes for as to against it, then the question is designated "on-topic" or "off-topic".
Commit - Users are then asked to "sign-up" and commit to the site, being an active member of the community by asking and answering questions.
Beta - At this point the site is in the critial phase - it is judged on how much the site is utilized by the users. Over the next 90 days, the site is evaluated, thus if the site is not being used, it s deleted.

After learning how to create a site, I pondered the question (sorry for the Field of Dreams cliche'), "If it is built, will fellow Alteryx users actively participate?" Before you answer this for yourself, here are my two compelling reasons for creating an AlteryxUser Area 51 Q&A site:

It's not company moderated. The group of dedicated individuals at Alteryx do a great job running their Alteryx forum, yet I feel the users need a site where all types of questions can be presented and openly discussed.
Reputation - The more users participate and bring in additional users, they recieve points towards their reputation. Reputation is a sort of ranking that is displayed on the site, in which the users earn through site collaboration by asking and answering questions and referring others to the site. Most importantly, users earn the most reputation when they follow through on their commitments. According to the Area 51 website, "Reputation is never given, it is earned by convincing other users that you know what you're talking about."

So I'm interested to see if there are enough Alteryx users and guru's interested in running this site. One user cannot an Area 51 site make, others must be commited to make this work.

Now, you might be thinking "Why bother when Ned Harding and the bright folks in Client Services answer my questions in the Alteryx Forum?" Good question, and the only response that I can give is that this is an Alteryx user only Q&A site, maintained and supported by Alteryx users. Nuff said!

So if you are interested in helping build this community, please click on this Follow the AlteryxUser Area 51 site link to follow the site (click the Follow It! button) and participate.

A Special Note: I'd like to ask employees of Alteryx to refrain from following this site until it clears the Beta phase - if it is to be, it's up to Alteryx users. Thanks!

Till next time, enjoy!

Alteryx SaaS and PaaS

2010-07-26T06:55:00.000-05:00

It's been a very busy summer and unfortunately not much posting going on, yet here's a quick post about an interesting article in the latest Retail Traffic magazine about the Alteryx Saas (Software as a Service) and Paas (Platform as a Service) offerings:

Click here to read the article

Also known as "Software on Demand", at a base level, you could simply say this is running Alteryx via the web, although stating that doesn''t really do it justice. To see this in action for yourself, demo the Alteryx FreeDemographics site. Look forward to alot of interesting news coming from Alteryx surrounding these offerings, as well as new posts coming from me shortly.

Now back to my summer - Enjoy!

Producing GeoRSS Feeds With Alteryx

2010-05-20T21:01:00.015-05:00

Hello again!

The topic for today's post came from a project I worked on, although it did not call for the data to be supplied in GeoRSS, it did get me thinking about ways to use Alteryx to provide geographic data via the web.

Thus the GeoRSS Feed Module above was conceived, primarily for creating basic GeoRSS point feeds. For those of you not familiar with GeoRSS, or even RSS (Really Simple Syndication) feeds, check out xml.com's "What Is RSS?", then hit the georss.org website to read about GeoRSS. Essentially, GeoRSS provides a way to publish and share geographic information using standard web syndication feeds. This is accomplished by extending existing RSS feed formats to include geographic information. There are different "flavors" or encodings of GeoRSS (Simple and GML); this module was built specifically for the generation of GeoRSS-Simple. GeoRSS-Simple is a very lightweight format which supports basic geometries (point, line, box, polygon) in the WGS84 coordinate reference system only. If you are looking for something more elaborate and to work with a different coordinate system, you would need to modify the module to generate the GeoRSS-GML encoding.

And while the encoding is called Simple, there is really quite a bit you can do with it. Here is a screen shot of a feed of Kansas City Sprint retail locations displayed in Google Maps:

Along with the coordinates and address info for this feed, I also included a URL link to each store photo to include in the bubble.

The advantage of using GeoRSS is that I can pass this same feed to multiple web map applications, such as Google, Bing, Yahoo, Worldkit, and OpenStreetMap, just to name a few. There are a number of applications that can render GeoRSS feeds, so you have a choice as to which one you or your customer chooses to use. In order to make the feed available, use must copy the file to a public web server so it can be accessed. To create an automated feed, change the module to read data from your source location database, alter the Output Tool to save the file onto the web server, then add this module to the Scheduler, schedule a time for it to run and you're set!

Once the file has been copied to a publicly accessible web server, you can easily view the feed by passing it to the web map of your choice.

For example, to view this module's feed (http://allaboutgis.net/Alteryxuser/georss12.xml) on Google Maps, enter this URL into your browser (or click on the link):

http://maps.google.com/?q=http://allaboutgis.net/Alteryxuser/georss12.xml

or in Bing Maps (shown below):

http://www.bing.com/maps/?mapurl=http://allaboutgis.net/Alteryxuser/georss12.xml

I started first with a file that contains these fields: ID, Name, Address, City, State, Latitude, Longitude, and Photo URL. Then the xml file was essentially "pieced" together by using the HTMLPassThrough Tag in the Text Tool in Expert Mode. I broke the entire file into three parts; the beginning, the body, and the end. I used the Regex Tool to remove any carriage return or new line characters after generating the XML for each section to keep from being confused with XML syntax, reserved characters need to be replaced with their respective character entities. Examples of reserved characters are the ampersand (&), quotes (") and the greater than (>) and less than (<) brackets. These translate into &, ", <, and > Note I used the Formula Tool to replace any ampersands that might be included in the Name and Address fields to character entities. Also, take special note at how the html used within the location bubble is formatted, indicted by the tag.

The body of the GeoRSS file was created using the Block Until Done Tool so all the locations were created as one prior to being unioned with the beginning and ending sections of the file. When this happens you will get Warnings stating that fields are not present in all outputs. This is ok in this instance, the Union Tool is uniquely used in these instances so you utilize the Set a Specific Output Order option. This makes certain that the section are unioned together in the correct order. After this, the beginning and ending of the tag is be removed from the text using the Formula Tool since they are no longer needed once the text exits the Text Tools and moves downstream. The Select Tool is used to removed the additional Text fields, the Regex Tool is used once more that make certain no new line or white space characters remain, then the text is output and saved with a .XML extension. The same could be done to output GeoJSON, the process is the same with just a bit different syntax.

If you believe you can use this module, or if you would just like to see how it was constructed, send me an e-mail to ronh@allaboutgis.net, and put in the Subject "Feed Me GeoRSS Module". If you improve on it, let me know so we can all learn.

Till next time, enjoy!

Ron

Generating Interactive Web Pages In Alteryx Using HTMLPassThrough

2010-04-30T08:24:00.010-05:00

Hi All!

Well its been a while since I posted last, but I've been having some fun with what I've learned at Extend! One of the activities I like best at each conference is called the Solution Center.

This conference event is where Alteryx users can get questions answered about any Alteryx product. Here you can meet with Alteryx Developers and support teams in person and ask for assistance about any issues you're trying to resolve. Where else can you sit down with Ned Harding, Lead Alteryx Developer, and talk about working with the software, or brainstorm with Jim Schattin, Rob Bryan (Alteryx Guru's) and Adam Riley (fellow Alteryx blogger) about a unique feature?

These discussions came together like this - I was in the Solution Center one day and asked Ned about using Alteryx to generate interactive web pages, and he told me about the little documented PCXML HTMLPassThrough tag. This tag is used within the Text Tool in Expert Mode (more on this in a bit). Shortly thereafter, Jim, Rob, Adam and I sat down and started working with it. The module displayed to the right is the result. A little playin' with the Google Maps and StreetView API's, a bit of JavaScript, and a touch of HTML, and you have what I call the 'Cuppa Joe' Finder! Not much to the overall module, yet I think the results are great! The result is the web page you saw at the top of this blog.

Here's how it works...

PCXML is Alteryx's proprietary file output format that provides a standard reporting structure across all of its products. This is how the Render Tool can output into multiple formats; it easily converts the PCXML into the output format you desire. Yet when outputting to HTML, certain code and tags (like JavaScript and HTML iFrames) are not recognized within PCXML, so the HTMLPassThrough tag was created to allow for "raw" text. This is text which is not converted into PCXML and left alone. To use this tag, use must enclose the "raw" text within beginning and ending tags (... ). This also will only work if you toggle the Expert Mode radio button on the Text Tool.

This opens up a whole new use of Alteryx for me, and was exactly what I was looking for. My preference is to provide users with interactive web pages, not static ones. Yes, you might say that you could use a web development language to do this, and I agree there are better ways to accomplish to create interactive web content. Yet I also say that I do not have to know a web development language because I can now have Alteryx do some of this for me! That said, it is not nearly as flexible and you still need to know a bit about JavaScript and HTML, and like in this example how to work with the Google Map and Streetview API's. Regardless, there is alot that can be accomplished with this tag.

For example, if this web page represented a company store finder, this module could simply be scheduled to run often to reflect the changes in store locations and post the updated pages on the web server. Unfortunately, since it is not dynamically generated content, each page would have to be loaded to the server taking up what could be considerable disk space. Anyway, look for more posts from me using this tag in the near future. I believe Adam might post something about this as well on his UK Alteryx Blog.

A short note about the Google map and StreetView window; if you have the Google Earth Plugin you will get the option to view the map in "3D" or Earth mode like it is shown in the page screen capture above, but only if you have it installed. Time to go - If you would like a copy of this module, simply e-mail me at ronh@allaboutgis.net. Just put in the Subject line "Send Me the Cuppa Joe Module Now!" and I'll reply with the module.

Till next time, enjoy!

BTW, have you sent in your donation for World TEAM Sports and the Adventure TEAM Challenge?

SRC Extend 2010 - A Whole Lot Going On!

2010-04-05T10:06:00.002-05:00

It became fairly obvious to me that trying to get a post out everyday of the conference was tougher than I thought, so this we be a high level wrap up of Days Two and Three. This post is chocked full of links to other sites, so bear with me.

Day Two of the conference kicked off with the Alteryx Developers Forum. Ned Harding talked about writing Effective Macros, as well as some insights to what is coming out in Alteryx 6.0 (future post in the works here...). No sense in duplicating efforts here, so read up on what was discussed at the Developers Conference on the Alteryx Team Blog.

And now for the primary topics of this post. First off, the big news on Day Three was that Spatal Re-Engineering Consultants, or SRC, has changed their name to Alteryx, L.L.C. It has a nice ring to it, eh? Read more about this on the blog and check out the new company website Alteryx.com.

Also, Day Two at the conference included the running of the Alteryx Grand Prix. More on the winner of this year's event later in the blog. For those of you who are not certain what the Alteryx Grand Prix is all about, here is a video featuring Tara McCoy, Alteryx Product Manager explaining a bit about it:

Sorry about the jumpy video, I'll do better next time! As Tara mentioned, the winner gets wins a trip for two to Las Vegas and the Mario Andretti Racing School! A "good" video does much better than words here - check out last year's winner Scott DiGiacinto from KOREM Corporation and his experience at the Andretti Racing School via a link from Sinam Al-Khafaji's blog here:

The contest modules for this year's Grand Prix were more centered on data manipulation and processing that last year's spatially focused modules, which can obtain from a previous post here. After a very close "race", the winner of this year's Grand Prix was Jeremy Kane of Efficax Data Systems. Here is a good photo of Jeremy on the winner's podium by Gary Wood:

The second place winner was Jason Dunkel of Environics and in third was Cameron Steele from Buxton. Congratulations to all the Grand Prix participants!

That's all for now, till next time enjoy!

Ron

SRC Extend 2010 - Day One

2010-03-24T07:16:00.001-05:00

Monday, March 22nd was the first day of the SRC Extend 2010 conference. Hundreds of Alteryx users have gathered for three days in Broomfield, CO for the event, For those of you who were not able to make this one or have not had the opportunity to attend a past Extend conference, I'll try to provide you highlights and tidbits for each day of the conference.

To start things off he first ever Alteryx User Group meeting was held with a number of presentations from different users from the US and England, Three Alteryx blogs were mentioned; this one, Adam Riley's UK blog, and a new entry that Dean Stoecker pointed out to me; the Lean Six Sigma and Continuous Process Improvement blog by Nari Kannan. A fresh new look at Alteryx from a Six Sigma point of view, and I look forward to what Nari has to say from this prespective. Adam continues to provide new blog posts that are very interesting - I especially like his Chess game that he created within Alteryx. Both blogs are referenced in my list of blogs I follow on the right-hand side of this page.

More to follow as the conference continues, so stay tuned!

Enjoy!

Working With GeoJSON Files in Alteryx

2010-03-18T13:10:00.009-05:00

Although my preference now is to typically work with yxdb and cydb file formats, I find it liberating from time to time to be able to work free of vendor specific file formats.

That's why I'm excited to hear about the growing acceptance of multiple light-weight data interchange formats. These formats, or more formally Object Representations, allow geographic data structures to be saved out as simple text and then imported into or exported from a variety of GIS applications. That said, I also tend to gravitate towards vendors that allow you to work in multiple formats and not those that force you to work in their own specific, proprietary format(s).

So I was not surprised to find out that SRC added support for one of these data interchange formats in Alteryx 5.0, which is called GeoJSON. GeoJSON is a geospatial data interchange format based on JavaScript Object Notation (JSON). Use of this interchange format provides the ability to express spatial objects as variable length strings, which can then be saved out as a simple csv text file with a .gjsn extension.

If you want to learn more about GeoJSON, here are two good articles on the topic. First, you can read Linda Thompson's post about the GeoJSON addition on the Alteryx Team blog here. Then read a great article by Christopher J. Andrews from Autodesk that was published in Direction Magazine called "Emerging Technology: AJAX and GeoJSON":

Click here to read Christopher Andrews Direction Magazine Article on GeoJSON!

Now that we know a bit more about this format, let's look at how to import and export a GeoJSON file into\from Alteryx. I ran across this while working on a project with GeoJSON files, so I queried Ned Harding who provided the following process. To begin, drag an the Input Tool into a blank module and change the Files of Type selection to All Files and browse for your file, as I have in the image below:

Since GeoJSON is not a standard file type, Alteryx will try to resolve the file. To read the file, you'll need to set the Read it as a built in type selector to GeoJSON (gjsn:) as shown in the next image:

After you select the OK button, the Iput Tool properties window will only show one spatial object like the image below displays:

Now the polygon can be browsed and used like any other spatial object, yet it was read in from a text formatted file - a considerably smaller, non-proprietary GeoJSON file I might add! So if your looking to import spatial objects from a GeoJSON file into Alteryx, the process is rather straight forward.

Exporting GeoJSON files out of Alteryx is a bit different, yet just as simple. The main difference is that the spatial objects you are exporting are saved out as a V_String, or variable length string. This conversion is easily accomplished with the Select Tool:

Note: If you are exporting multiple polygons as one gjsn file, use the Summarize Tool to combine the polygons into one, like I did with my Canada example I used for this post (see top image). This must be done in order for the file to be save out correctly. Then output the file out as a csv file (with a .gjsn extension of course) with \0 as the delimiter with no header as shown below:

That's all there is to creating GeoJSON files in Alteryx. I realize that this is somewhat of an obscure topic, yet it might come in handy someday when you are are given GeoJSON files to work with, or need to provide a deliverable in this format.

I'll be heading out next week to the SRC Extend Conference, so look for continuing posts during that time.

Till next time, Enjoy!

Ron

REGEX - Check Out the UK Alteryx User Blog!

2010-02-21T21:05:00.006-06:00

Howdy Gang!

Are you getting ready for Extend 2010? Or are you like me and trying to figure out a way to convince upper management to send you? Well, if you are unable to make it and you are looking for a different take on how to get the most out of Alteryx, check out Adam Riley's UK Alteryx Users Blog from across the pond. Adam has started a detailed Alteryx functionality blog and I think it is great! I'm always interested in how other users are using Alteryx to solve different business analysis issues.

I've added Adam's blog to the list of blogs that I follow - see the top of the list on the right below. Although there is not alot of content yet (it's only a matter of time!), please show Adam your support by following his blog. His latest post deals with the multifaceted Regex Tool; take a look at his example to get a glimpse of what you can do which this powerful function.

More on Regex, there is also a good Regex Replace tip in the May 2009 SRC Customer Advantage Group Bulletin explaining how to save an often used Regex function as an expression in the Formula Tool. For quick debugging of Regex expressions, go to the Regepx Editor to test your expressions before adding to your Alteryx module.

And while I have not found any Webex's specifically on the Regex Tool, I've added a quick link on this blog to the SRC Alteryx WebEx Links page. This page is chocked full of Alteryx WebEx training sessions on a large number of topics.

Well, that's all for now. Enjoy, and remember to check out Adam's blog!

Ron

Is an Alteryx Certification Program Overdue?

2010-01-14T05:50:00.001-06:00

With all of noise recently about making Alteryx viral, wouldn't you think that another way to promote the product would be to offer an Alteryx certification program. So the question is SRC, where is such a program? Yes, yes, I know that SRC has been working on a certification program for some time, yet I believe the time is overdue.

My thoughts are what better way to show your appreciation to your existing Alteryx users than to create a certification program. Give them something to inspire towards. Offer a certification program to Alteryx users to become better and more knowledgable, and in turn promote it's use. Given the present economic environment, its hard to say if certification would indirectly translate to more licenses being sold (this may really be the only real incentive for SRC to do this), regardless it seems to me that it will be up to the current user base to require SRC to roll one out.

So, I've started a small poll to see how many of you would be interested in becoming Alteryx Certified. Some of you might be excited about this, some could probabaly care less, yet I'm interested to see if I'm on the mark or way off base about this. So vote, voice your opinion on this. If you do not vote, then at least respond to this post stating why.

Till next time, enjoy!

Honeycomb's big...yeah yeah yeah!

2009-12-16T02:36:00.013-06:00

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

2009-12-01T06:05:00.002-06:00

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!