tag:blogger.com,1999:blog-72605884247991136372024-03-13T07:52:38.101-07:00Tableau Tips and TricksAnil Batrahttp://www.blogger.com/profile/10987449618439416854noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-7260588424799113637.post-79560249744808753642015-03-20T16:08:00.001-07:002015-03-20T16:09:50.105-07:00How to create a union between two excel sheets<div dir="ltr" style="text-align: left;" trbidi="on">
<a href="https://www.blogger.com/blogger.g?blogID=7260588424799113637" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"></a><a href="https://www.blogger.com/blogger.g?blogID=7260588424799113637" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"></a><a href="https://www.blogger.com/blogger.g?blogID=7260588424799113637" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"></a>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.<br />
<br />
Example: If you have<br />
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:<br />
<a href="https://www.blogger.com/blogger.g?blogID=7260588424799113637" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"></a><a href="https://www.blogger.com/blogger.g?blogID=7260588424799113637" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"></a><a href="https://www.blogger.com/blogger.g?blogID=7260588424799113637" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"></a><br />
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).<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-YnHjb2TJWXE/VQyoIZd6tfI/AAAAAAAACSE/gkiQAY6X-zE/s1600/tableauscreenshot.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-YnHjb2TJWXE/VQyoIZd6tfI/AAAAAAAACSE/gkiQAY6X-zE/s1600/tableauscreenshot.jpg" height="220" width="400" /></a></div>
<span id="goog_629873051"></span><span id="goog_629873052"></span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
Next screen you will notice that there is a new option under the sheets called "New Custom SQL" (see below)<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-4eMKCO753lQ/VQyoIS5cqFI/AAAAAAAACSk/9stlhb3s2Sg/s1600/tableauscreenshot2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-4eMKCO753lQ/VQyoIS5cqFI/AAAAAAAACSk/9stlhb3s2Sg/s1600/tableauscreenshot2.png" height="263" width="400" /></a></div>
<br />
<br />
<br />
<br />
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.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-GSN3qD28Q6w/VQyoI4W8-2I/AAAAAAAACSM/t26FVqtRsQ4/s1600/tableauscreenshot3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-GSN3qD28Q6w/VQyoI4W8-2I/AAAAAAAACSM/t26FVqtRsQ4/s1600/tableauscreenshot3.png" height="262" width="400" /></a></div>
<br />
<br />
<br />
<br />
Click Ok, and you have your result set:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-y_YJ0_yGZHQ/VQyoJMmcJsI/AAAAAAAACSQ/ggKp4mRNf8I/s1600/tableauscreenshot4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-y_YJ0_yGZHQ/VQyoJMmcJsI/AAAAAAAACSQ/ggKp4mRNf8I/s1600/tableauscreenshot4.jpg" height="261" width="400" /></a></div>
<br />
<br />
<br /></div>
Anil Batrahttp://www.blogger.com/profile/10987449618439416854noreply@blogger.com3