Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
Alteryx Partner

In part one of the series Should I Stay or Should I Go, I started contemplating the future of my WFH home life, and if it made sense to stay in my apartment. The location is good, but I’m not going anywhere for the time being, and as the days get hotter and hotter I find myself wishing I could escape to an office space to get some A/C, and make life more comfortable for my current foster dog Hobbes.

 

1.jpg

 

 


I made a little application that helped me narrow down my search on apartments in the city, but it was only good for selecting fields I wished to investigate; Pets Allowed, Parking and A/C. But it didn’t let me choose which option I wanted in each category.

 

2.png

 

 


For example, Central Air, Individual Units and None were my selection choices for Air Conditioning, and I wanted a way to enhance my app to be able to make those selections. I did just that by transforming my List Box app to a Tree app.

 

I started by taking my previous application and removing the List Box option. It’s not as simple as replacing it with the Tree tool. A Tree input will allow users to make selections based on a hierarchical format; this format can be defined by a series of keys. I had to create a unique key field, one for each amenity field, and another for the selection of each. I needed two levels of keys to encompass all my selections.


I found a helpful guide on the Alteryx Community as to not reinvent the wheel. 

 

3.png

 

 


From the Select tool, I added a Crosstab tool to organize each apartment ID by amenity type.

 

4.png

 

 


I sorted the Name and Value in ascending order, then decided to eliminate any empty fields by filtering them out.


Next was the key to creating my hierarchy. I brought in the Tile tool to create a unique value for the Name (Amenity) and another for the Name AND Value.

 

5.png

 

 


This allowed for two levels of key values.

 

6.png

 

 


I eliminated the sequence numbers and kept only the Tile numbers, and named them Level 1 and Level 2. I knew I needed to combine them, but I had a unique situation here. I needed to combine both levels to create my key, however, I had more than 10 amenities.

 

For example, if I combine Level 1 =1 and Level 2 = 11, I get a combination 111, but if I combine Level 1 =11 and Level 2 =1, then I also got a combination of 111. This will be errors in my results. In order to make sure each key is truly unique I pad the left-hand side with 3 zeros.

 

7.png

 

8.png

 


This output was used in my application.

 

Before that, I needed to complete my key field. I stacked both Level 1 Key and Level 2 Key on top of each other with a Union tool.

 

9.png

 

 


The results were summarized for each Name and sorted in ascending order by before the Description and Key were output.

 

10.png

 

 


Let’s take a moment to understand the results.

 

11.png

 

 


My first amenity option is Air Conditioning Type. The Key is 001, the hierarchy will then include Central Air (001001), Individual Units (001002) and None (001003). Next, the hierarchy will restart with Balconies (002) etc. This output will be used when setting up our application.


Back to the output from the Formula tool, I Transposed the data so I could have two layers in my Tree; stacking the Level 1 Key and Level 2 key on top of one another. This information was filtered out based on the selection I make from the previous key output.

 

12.png

 

 


A quick renaming of my fields with the select tool and I was ready to create the app!

 

13.png

 

 


As I mentioned, I decided to follow along with a helpful filtering configuration to display my amenities based on a filter, so I added a formula to a Filter tool.

14.png

 

 


Next, the Tree tool was added and attached to the filter. The Tree tool displays which amenity the user should choose, based on those key fields that were created. I added that file that contained the keys, so the display of the tree tool is recognized.

 

15.png

 

 


The Action button was set to filter out any keys I did not choose, by updating a Formula.

 

16.png

 

 

17.png

 

 


The finished connection should look like this:

 

18.png

 

 


The output was combined with the results with the original data so I could have the address and ward information for the corresponding buildings. I deselected the amenity fields on the right because that will be brought in with the Tree Tool.

 

19.png

 

 


The result was a dynamic way to search through building amenities. Use the Magic Wand to test the App.

 

As I mentioned, I wanted a place with Air Conditioning, Parking and Pet Friendly, so I chose my preferences and run the App.

 

20.png

 


Now, not only do I have my desired amenities, I also have the desired selection.

 

21.png

 

22.png


Now I can finally determine what places suit my needs. But the real question is, where are these places? I wanted to map out the areas of these apartments, and I have Addresses and Ward Information.

 

I brought in shapefiles and joined them with my results. I took a summary of how many apartments were in each ward, then I used the Reporting tools to set up a map that showed a distribution of apartments across the different Wards.

 

23.png

 

24.png

 

25.png

 

 

Getting closer!