The a**3 Blog (all about Alteryx)

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!

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

2009-11-26T20:15:00.003-06:00

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

2009-11-12T05:37:00.017-06:00

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?

2009-11-10T04:48:00.015-06:00

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!!!

2009-11-09T06:28:00.008-06:00

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

2009-10-29T04:57:00.008-05:00

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

2009-10-26T03:36:00.003-05:00

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!