Spatial Data - Part I - Data Parsing in Alteryx

by Emily Chen

While working on my spatial project, I found a useful technique for data parsing a field with 2 different formats (commonly seen in reports).

Side note: You’ll notice that my files are labelled “Brittany’s Activity Data”. One of my friends in Calgary, CA was kind enough to give me her running data for the last 2 years. She’s stopped for the last couple of months but hopefully we’ll find some insightful info to get her to run again! If you’re interested, she uses the Garmin system for her run tracking.

First step was to download all those kml files from the Garmin portal and configure Alteryx to port all the files from this designated folder.

input tool- brittany activity 1

Here’s what it looks like after I’ve imported the .kml files. Her individual runs stack on top of each other for a table of 25K records.

browse after input- brittany activity 1

 

There’s 2 key problems here (the second of which I didn’t discover until yesterday morning). The first problem is that there are 2 different file formats and pieces of data in the same column (see the “Name” field where it includes data about 1) when Brittany started/ended her runs and 2) her run times, heart rate, elevation and max speed during the lap. We see this commonly in reports that have been ported into reports. I felt this was a good “data wrangling” moment haha.

The solution? Separate the data into 2 segments and treat separately. When Carl (one of our superstar consultants!) first suggested this, I couldn’t wrap my mind around it. If this is also you, here’s the workflow.

 

filtering 2 different formats

 

Here’s what the files look like after the the transformation

File for when the “Lap?” field equals “0”

Data segment - 0

 

File for when the “Lap?” field equals “-1”

Data segment for -1

 

 

Once I got here, I “text-to-columns” the Description field (away I go!). Hopefully this helps someone facing a similar “Data Wrangling” scenario a solution 😀

Anyone figured out what the second problem was? Stay tuned for the another post!

 

My experience with PowerBI

3 mins read

Mon 02 Nov 2015

Understanding student dropouts through Sankey Charts

5 mins read

Sun 04 Oct 2015

2 Things I Learned from the Tableau Fundamentals Class

3 mins read

Thu 01 Oct 2015

Creating a Dynamic Sales View based on Different Date Types

1 mins read

Mon 21 Sep 2015