Friday, March 20, 2015

How to create a union between two excel sheets

Tableau does not have a join functionality that works like a SQL Union.  If you have the data spread out in multiple excel sheets and you want to see a concatenated view then you are out of luck as far as default joins are concerned.

Example: If you have
Date and Sales data of Jan in one sheet and Data and Sales data of Feb in another sheet then you can't join them using date or any other column. Let's assume that the sheets are so large that you can't combine them in one sheet, which restricts you to join via tableau only.  How do you create a data view so that you see a combined view of Jan and Feb sales data?  Here is the solution:

In Tableau choose "Microsoft Execl" in connection.  In the next window (the pop up to select the file), choose "Open With Legacy Connection" (it is under "Open" dropdown - see the image).



Next screen you will notice that there is a  new option under the sheets called "New Custom SQL" (see below)






Click on the "New Customer SQL", you will get an empty popup window. In this window type a SQL shown below, replace [Jan$] and [Feb$] with the names of your sheets. Make sure to add $ to then end of the sheet name.  Make sure both the sheets have same number of column else it won't work.





Click Ok, and you have your result set: