cancel
Showing results for 
Search instead for 
Did you mean: 
Announcement | Welcome to Alteryx Academy! We hope you enjoy the journey. Let us know how we can improve your learning experience by completing this survey.
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.

Challenge #78: Find the Best Housing Markets

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!

WeeklyChallenge78.JPG

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
Alteryx Certified Partner

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.

Workflow.PNG

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:

Analytic Workflow.PNG

Results.PNG
Community Administrator
Community Administrator

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

 

 

Jacques
Community Admin
JORGE4900
Asteroid

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
Bolide

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

Community Administrator
Community Administrator

@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.

Jacques
Community Admin
alex
Bolide

.twb 90.9 KB

.tde 72 KB

Firefox 54.0.1

Community Administrator
Community Administrator

@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

Jacques
Community Admin
Highlighted
nick_ceneviva
Alteryx Certified Partner

Solution attached

Spoiler
Capture.PNG