community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcement | Get certified today - take the Alteryx Designer Core and Advanced exams on-demand now!
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Weekly Challenge
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Unable to display your progress at this time. Please try again a little later, or contact an administrator if you continue to see this error.

Challenge #78: Find the Best Housing Markets

Sr. Learning Strategy Manager
Sr. Learning Strategy Manager

Last week's solution can be found HERE!

 

This week's challenge was brought to us by one of our own Community members and Weekly Challenge gurus: @NicoleJohnson. Nicole devised this challenge for her user group whilst looking for a challenge that would exercise many of the commonly used tools in Alteryx. If you ever have a challenge that you would like to be featured, please DM me!

 

As we all know, the housing market these days is going a bit crazy... we'd like to capture some census data on the value of new privately owned housing units in various metropolitan areas to see where the top 10 markets are YTD as of end of April (data lags about 2 months behind).

Our objective for this challenge is to capture Census data from the following URL: https://www.census.gov/construction/bps/txt/t3yv201704.txt

We will then need to parse the data into the columns identified in text input #2, then sort to find the top 10 new housing markets based on value.

 

BONUS:
Build an app that will allow you to choose whether you want to look at value (the "v" in "t3yv" in the URL above) or units (which would look like "t3yu"), specify the through-date (year & month designated at the end of the txt file name, in the example above as "201704"), and which column you want to sort by (Total, 1 Unit, 2 Units, etc.)

 

seattle.jpgThis location will be one of the top performing markets

Alteryx Certified Partner

Well, I'm almost there. I'm just trying to optimise the line breaks it puts in automatically. Anyone found a quick way to do that?

 

Spoiler
Screen Shot 2017-07-24 at 20.35.15.png
Sr. Learning Strategy Manager
Sr. Learning Strategy Manager

New start file added!

Alteryx Certified Partner
Alteryx Certified Partner

Thanks Nicole for the challenge.  :)

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Alteryx Certified Partner

I built the workflow first then changed it a bit to create the bonus App

 

Spoiler
challenge78.jpg
Alteryx Certified Partner

I went a bit overboard with the Regex on this one...but I was determined to Parse the whole thing straight from the download tool! I need to get better at my Regex to reduce the number of tools needed.

 

Spoiler
Weekly Challenge 78 output.pngWeekly Challenge 78.png
Quasar

@NicoleJohnson, this was a fun one!!!

Tried to be streamlined with my tools.

Spoiler
For the workflow, I copied the downloaded data into a text input for a streamlined process that I could play with while on the plane.

First step was to parse into rows on the \n character. Since that took some states to the next line, a multi-row formula tool looks for records that begin with the CSA & CBSA numbers, and looks below for any lines that begin with a state abbreviation. For those rows that match both conditions, the text of the two rows is combined.

Cleaned up all of the trailing, leading, and extra spaces to make parsing easier.

Next, a Regex parse tool, using the parse method, to separate into all of the columns.

A dynamic rename tool takes care of the headers.

For the app, added inputs to choose value, units, dates, and fed those into the URL for the data grab. Then asked for a column to sort on for the top values, and a control to choose how many top values (just for fun)

Finally, created a report so there was some nicer output from the app.

Highlighted
Spoiler
Challenge 78 1.PNGChallenge 78 2.PNGchallenge 78 3.PNG

Mine is quite clunky but it works. 

 

Bolide

Solved w/o going down the RegEx path.  I didn't attempt the bonus, but I did output into Tableau to allow you to select top 10, 25, 50 or what ever you choose for the top markets.

 

Spoiler

 The beginning of the workflow was pretty much the same as the other week 78 posts.  I went with data cleanse tool and identifying rows to reformat based on recurring patterns in the data that were exposed in the cleanse process.  I used a path to fix the names and another path for the numerical data.  I joined them back up with the headers and then sorted and filtered the top 10.  I also sent the entire output into tableau and created a map with a data layer showing estimated population growth % by county 2017-2022.  This may show if there is a disconnect between new construction and estimated population growth.
wk78_1.JPGwk78_2.JPG

wk78_3.JPG

wk78_3.JPG 

Bolide

It wouldn't let me attach the tableau workbook.  PM me if you'd like me to email you a copy.  The error received was "The contents of the attachment doesn't match its file type."  Not sure why that is happening?