Combine multiple Merge / Union data sources
I have 4 excel files that have the same columns, they are just split by quarter. This is necessary because the automatic system cannot handle too large files.
In the table, I connected to each data source and named them Q1 / Q2 / Q3 / Q4.
How can I get this to work like one year?
I researched joining and read articles talking about Custom SQL Unions. This doesn't seem to work, it only allows me to make connections between worksheets in the same workbook. These are different books and therefore different data sources.
source to share
Starting with version 10.1, you can use UNION worksheets from different Excel workbooks by using the wildcard lookup feature when you specify UNION.
However, Tableau can handle very large Excel or CSV files, so another option is to add multiple data files to one long file before using Tableau.
If your foreground tool cannot generate more than a quarter of the value at a time, it is not recommended to add files to one file (with one header line) before using Tableau, especially when using CSV. There are many ways to do this, but one free toolkit to simplify such tasks: csvkit http://csvkit.readthedocs.org
The second option is to load your data into a database table instead of a long CSV or Excel file.
The third option is to create a Tableau Data Extraction from one of the files and then add the data to the statement from the other files. This is minimal effort on your part, but make sure you understand how the extracts work first (and keep the original files when you need to rebuild your extract)
There is even a fourth option. Use the Excel copy workheet command (right click on a tab) to combine all your files into one Excel workbook with multiple sheets (tabs). Then you can use native SQL to combine the books with the UNION ALL command. But this will require using the outdated Excel driver for custom SQL to be available. This is not the option I would recommend for this use case, but it will work if you really hate the other options.
source to share
I prefer to work outside of Excel when it comes to most datasets. If I faced this problem, I would return the data to a format that Tableau can more easily work with using a local or hosted database. My preference is MySQL.
Download / install MySQL Server locally
Load into new table using sql code
or
Download MySQL Workbench to download the GUI
Second, I would look at splitting the data vertically rather than quartering to allow data blending or table joining. If you have access to the structure of the report, select a primary key for all four files and split the fields between them. This can get ugly, so I'll stick with the local database option if possible. Hope this helps to tempt some ideas.
source to share
Combining CSV files is now (since version 9.3) part of Tableau functionality: https://www.tableau.com/about/blog/2016/1/combine-your-data-files-union-tableau-93-48891
You can also vote for my Extract union idea: https://community.tableau.com/ideas/7076
source to share