#Tableau prep join vs union manual

To solve my fuzzy matching problems AND to have a scalable solution, I then turned to Anatella. It’s now time to tell you about the second solution. This is possible when you have a few entries, but the method would not have been scalable.

#Tableau prep join vs union manual

I should have made a join, then a grouping, and finally a manual deduplication. The concern is that groupings are only possible on a single table. The algorithm used is called Metaphor 3 (open source, see here). Groupings are useful within the same table to detect variations. Some research later, I came across this article describing a method of grouping by pronunciation. It remains to be seen if a fuzzy join between tables was possible. I have highlighted in yellow the ones I was talking about above. The advantage of Tableau Prep Builder is that the ETL process is graphical and problems are evident (see below, Tableau Prep automatically puts them in red). But I needed a more elegant solution.Īs I intended to visualise my data in Tableau, the first solution I tried was to use Tableau Prep Builder.

In short, as you will have understood, I could have spent a few hours cleaning my database and having the entries “checked” so that the join could work.

“Central African Republic” and “Central African Rep.”īefore I was hounded by the “search and replace” fanatics in Excel, I was looking for a solution that would be more economical in terms of transformations.

“Antigua and Barbuda” in one file, “Antigua & Brabuba” in the other.

“Cabo Verde” in the national language of the country, “Cap Verde” in English.

The problem is that a country name is far from being a constant. I found this database on the website of the World Trade Organisation.

I preferred to look for a database of the different countries and the “official” region to which they are attached. I could have made groups of countries directly in Tableau, but when you have 200 entries, it is tedious (and not necessarily error-free). It, therefore, seemed appropriate to me to visualise these flows at a higher level of granularity: the region of origin. So, on one side, you have about 200 countries of origin, and on the other side about 30 countries of destination. The database details the number of migrants according to their country of origin and their country of destination. Solution 2 with Anatella (spoiler: it works)Īs part of a personal project on the visualisation of migration flows in Europe, I obtained figures from the European Union (the database has 242500 lines).

Solution 1 with Tableau Prep Builder (spoiler: it doesn’t work).