Challenge #78: Find the Best Housing Markets

[NicoleJohnson]

So cool to see the variations from everyone!! When I demo'ed this in our User Group meeting a month or so ago, I had a solution all prepared (in case there were crickets in the room). We ended up coming up with something totally different as a group... and then when I went through it again this time, focusing on creating the app version, I came up with yet another way to get to the same answer. Go figure! Gotta love how Alteryx even makes you rethink the path to a problem you came up with... :)

Spoiler

I worked out (stressed out) my brain on this one by refusing to use Input #2 and figuring out what I would need to do to get the headers correct even though they were so obnoxiously formatted with the extra lines for the multiple unit columns. Got a solution... then realized the headers were in different rows when you download the data by number of units vs. value. Ergo, this workflow ended up waaaaay more complicated than my original solution. But all roads lead to Alteryx solutions, and I got there eventually!

1. Download the data, and count the records to determine total rows (so I know which row #'s to exclude at the end since I dumped the last 3).
2. Determine rows that had "false" newlines due to text that wrapped around to the next row. Used my favorite underdog tool that I never remember to use, the Make Columns tool, to bring the wrapped line to the same line as the original, and then concatenated together.
3. Parse the data using one beastly 8-field RegEx parse expression. My only regret with this workflow is that I didn't find more places to use RegEx... :)
4. Deal with the awkwardly formatted column headers by bringing in specific rows from the data, parsing data with RegEx, and creating the column headers with Dynamic Rename.
5. Union data + column headers, sort by the specified column, sample specified number of records, create table & report text, and output results!

Thank you, @JoeM, for the chance to be on the other side of the challenge for a change! And I am, as always, in awe of the innovative answers received... Cheers, everyone! 

NJ

[mceleavey]

Cheeky bit of text parsing.

Spoiler

I first did this without the bonus, simply parsing the text output of the URL to rows using Regex, specifying the beginning of the line to a carriage return (^.*?\r).
I then needed to fix the records that would split across two lines. To do this I used a multi-row formula which rejoined the records to the line above where applicable then dropped the duplicate.
Then it was simply a case of sorting and sampling the top ten records.



Once I'd done this, I simply added a second column to the input file, with the values "Value" and "Units", which I then used as drop-down selection, converting to an analytic app and allowing the selection to drive which URL would be used on the download:

[JacquesC]

Hi @alex

I have added all the tableau file extensions in the file extensions for attachments:

.twb .twbx .tds .tdsx .tde .tbm .tms .tps

These ones were missing before: .tbm .tms .tps

Is there another extension that we are not aware and needs to be added?

Thanks

[JORGE4900]

By taking a look at how others solved this problem, I realized that the text document came with invisible break lines that helped separating the rows; I found it a lot easier separating two chucks by the only comma within each row; on this way, I could get the 3 fields on one side and the remaining fields on the other side.

I did not complete the application but noticed that depending on the text pulled, there were 5 or 6 columns of value information; some files did not list the 6th value column.

[alex]

I tried loading both the workbook and extract together and separately, but received the same error message.  Running on Tableau 10.3.

[JacquesC]

@alex Please can you give us the extension of the file that you are trying to upload and its size? Can you also let me know what browser do you use for uploading it. Thanks for your patience.

[alex]

.twb 90.9 KB

.tde 72 KB

Firefox 54.0.1

[JacquesC]

@alex Sorry that you are still having this issue. I tested attachments with these two types of files and I did not see any issues on this board and on the community. I sent you a PM. May be we can try to solve it via a Webex call. I will be pleased to help you and finally determine what is causing this issue. Thanks

[nick_ceneviva]

Solution attached

[patrick_digan]

Spoiler