Tableau ETL

Some people get scared when I say that I use Tableau as an ETL toll. Well sometimes you need to be creative in order to quickly solve some challenges and get what you want.

This was the case on the latest #makeovermonday submission. Since I saw the dataset on Irish Whiskey exports I wanted to try building a origin-destination map (that I find really cool). So I went googling and found the own Tableau help for this.  The second option was the one that match my vision and this is the format I had to have the data on:

date-origin-destinationSo I looked the dataset provided by Andy and basically found all information there, but not exactly in this shape:

fields
With a few calculated fields in Tableau I had the data I needed in a worksheet:

[Origin-Destination]
IF [Country] = “Ireland” THEN “Origin” ELSE “Destination” END

Translation for humans: “Hey Tableau, when the country is Ireland, make it the Origin, for all others, they will be the Destination of all whiskey”

[Path Id]
IF [Country] = “Ireland” THEN [Country]+”-“+[Country]
ELSE “Ireland-“+[Country]
END

Translation for humans: “Hey Tableau, when the country is Ireland I need to have a dumb ‘Ireland-Ireland’ path, but the for the others the format is Ireland first and the Country next (don’t forget the – in the middle)”

And here is the worksheet:

worksheet
The next step was to export to Excel and for that you just need to right click and Copy the data to paste in Excel:

export-lat-longThe exported data looked like this:

excel-dataI then processed it all in Excel:

  1. Removed the regions as they don’t have Latitude and Longitude
  2. Removed the Measure Names column
  3. As you can see I am missing the “Origin”in the “Origin-Destination” column. For that I just copied the cells, set the Country as “Ireland” for them all and replaced Destination with Origin.
  4. At least, I’ve added a very important field that is not in the description: Path Sort (1 for Origin, 2 for Destination). This was a help from Zen Master Lilach Manheim because it plays a factor when Tableau draws the connection between these ends.

Here is a view of the final Dataset:

final-excelNow I could follow the instructions from Tableau blog, then sort the Path ID field by the Path Sort field and all was done. Here is the viz after all formatting was done:

USA TOPS IRISH WHISKEY EXPORTSWhat about you? Have you ever used Tableau to format and extract data from?

Note: I would invite you can click on it to see on Tableau Public (which you can) but the Font won’t resolve so it will look ugly as hell.

Advertisements

One thought on “Tableau ETL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s