Wednesday, 6 January 2016

Joins in tableau

  • Joins combine related data from different tables.
  • When working with a relational database, such as Microsoft SQL Server or a flat file such as Excel, our data are often stored in different tables within our database. 
  • We can combine data from multiple tables in Tableau using Joins.
  • For example, imagine that we have two tables. The first contains Patient Names and Patient IDs. The second table contains each patient’s prescription information as well as Patient IDs. We can use the Patient ID field to link the two tables into one table. This allows us to see the patient names alongside their respective prescription information.





Join Requirements:-

1) To create a join it is necessary to have a common field between the tables (Primary and Foreign Key), in this case we have Patient ID.
2)To create joins in Tableau, our tables must all be present in one single database. When using Excel the workbook represents the database and each sheet within the workbook is a different table


Types of Joins:-


  • The types of joins that you are able to make within Tableau depend on the capabilities of your database. 
  • For example, Excel files are only capable of making Left Joins, Right Joins, and Inner Joins, while Microsoft SQL data can also have an Outer Join. 

Joins types are best understood via a Venn diagram as seen below




Examples:-



Other Joins

There are two other techniques for combining data in Tableau.
1)Unions   2)Data Blending

Union:-
  • Unions append data to a dataset, like stacking one table on top of another. 
  • If we were to create a Union of tables 1 and 2 below, we would get the Unions results table containing all of the information from both original tables

Rule:-

  • For Unions to be possible, both tables must have the same number of columns with the same column names and same datatypes.

Note:-

  • A Union can be achieved by creating an extract of a table and using the “add data from a file” option. They can also be done using the custom SQL dialog and writing a Union query between two tables in the same database
Datablending:-

Data Blending is similar to a Join in that it combines information from two tables based on a common dimension.

Thanks Guru's,
Prazval.ks


Finding Student Best Subject

Lets achieve this using LOD's.  For this, We need to create 3 calculations, to find out the best scoring subject of each student ....