You found a great chart on the internet and now you are eager to do analysis based on what you see. You even think few steps ahead and imagine how you can play with the data once it’s updated, let’s say in a week or month time, so that you can pick up some trends.
There are only two setbacks here: 1) the data might not be in a format that is easy to use, and 2) you must repeat whatever you plan on doing with the data every time it gets updated on the internet.
Luckily there is an easy approach to this (well, easy is a relative term, but still!).
Follow me with this step by step guide and at the end you will be able to play with the ARIA (Australia’s Official music chart) in Alteryx.
- First thing’s first: Input the Url where you found your awesome table and download the data using the Download tool. This will create another field where all the code will be pushed to.
- Next, you need to create rows out of your data, so that it starts to look like a table. Add the Text To Column tool, select the DownloadData from the dropdown menu on Field to Split, set the Delimiter to \n and select the Split to Row radio button.
- Clean up the mess with the select tool and keep only the DownloadData field (deselect all other fields).
- And now it’s time for some RegEx! We will create two Regular Expressions to separate the relevant data from the code: Artist name, Item title (aka Song) and Position. By using RegEx testing tools (try: https://regex101.com/), we can identify the lines of code that hide our precious information. In this case it was the following two:
\s+<div class=”(.+)”>(.+)</div>
<span>(\d+).+
Don’t forget to choose the Parse option under the Output method.
- Filter out all the nulls and keep only the data that has Item title, Artist name and Position.
- Now just once again clean up all the fields and keep what you need. By using Join Multiple tool, join your data back by Record position. Remember that all the time we were using the order we inherited from the code itself, so there shouldn’t be any changes or errors in there.
And Voilà! You have the ARIA chart ready to use in Tableau!
The beauty of this is that next week the only thing you need to do is copy the url and past in in the Input tool.
Let’s see if Drake will still be number one!
