3 Union Your Data
You can union your data to combine two or more tables by appending values (rows) from one table to another. To union your data in Tableau data source, the tables must come from the same connection.
Supported connectors
If your data source supports union, the New Union option displays in the left pane of the data source page after you connect to your data. Supported connectors may vary between Tableau Desktop and Tableau Server and Tableau Online.
For best results, the tables that you combine using a union must have the same structure. That is, each table must have the same number of fields, and related fields must have matching field names and data types.
For example, suppose you have the following customer purchase information stored in three tables, separated by month. The table names are "May2016," "June2016," and "July2016."
May2016
| June2016
| July2016
|
A union of these tables creates the following single table that contains all rows from all tables.
Union
| DAY | CUSTOMER | PURCHASES | TYPE |
|---|---|---|---|
| 4 | Lane | 5 | Credit |
| 10 | Chris | 6 | Credit |
| 28 | Juan | 1 | Credit |
| 1 | Lisa | 3 | Credit |
| 28 | Isaac | 4 | Cash |
| 28 | Sam | 2 | Credit |
| 2 | Mario | 2 | Credit |
| 15 | Wei | 1 | Cash |
| 21 | Jim | 7 | Cash |
Use this method to manually union distinct tables. This method allows you to drag individual tables from the left pane of the Data Source page and into the Union dialog box.
To union tables manually
On the data source page, double-click New Union to set up the union.
Drag a table from the left pane to the Union dialog box.
Select another table from the left pane and drag it directly below the first table.
Tip: To add multiple tables to a union at the same time, press Shift or Ctrl (Shift or Command on a Mac), select the tables you want to union in the left pane, and then drag them directly below the first table.
Click Apply or OK to union.
Use this method to set up search criteria to automatically include tables in your union. Use the wildcard character, which is an asterisk (*), to match a sequence or pattern of characters in the Excel workbook and worksheet names, Google Sheets workbook and worksheet names, text file names, JSON file names, .pdf file names, and database table names.
When working with Excel, text file data, JSON file, .pdf file data, you can also use this method to union files across folders, and worksheets across workbooks. Search is scoped to the selected connection. The connection and the tables available in a connection are shown on the left pane of the Data source page.
To union tables using wildcard search
On the data source page, double-click New Union to set up the union.
Click Wildcard (automatic) in the Union dialog box.
Enter the search criteria that you want Tableau to use to find tables to include in the union.
For example, you can enter *2016 in the Include text box to union tables in Excel worksheets that end with "2016" in their names. Search criteria like this will result in the union of May2016, June2016, and July2016 tables (Excel worksheets), from the selected connection. In this case, the connection is called Sales, and the connection made to the Excel workbook containing the worksheets you wanted was in the quarter_3 folder in the sales directory (e.g., Z:\sales\quarter_3).
Click Apply or OK to union.
The tables initially available to union are scoped to the connection you've selected. If you want to union more tables that are located outside of the current folder (for Excel, text, JSON, .pdf files) or in a different workbook (for Excel worksheets), select one or both check boxes in the Union dialog box to expand your search.
For example, suppose you want to union all Excel worksheets that end with "2016" in its name outside of the current folder. The initial connection is made to an Excel workbook located in the same directory in the above example, Z:\sales\quarter_3.
Include: If you enter *2016 in the Include text box and leave the remaining search criteria of the dialog as is, Tableau looks for all Excel worksheets that end with "2016" in its name inside the current folder.
In the diagram below, the yellow highlighted item represents the current location, that is, the Excel workbook that you created a connection to in the "quarter_3". The green box represents the tables belonging to workbooks and sheets that are unioned as result of this search criteria.
Include + Expand search to subfolders: If you enter *2016 in the Include text box and select the Expand search to subfolders check box, Tableau does the following:
Looks for all Excel worksheets that end with "2016" in their names inside the current folder.
Looks for additional Excel worksheets that end with "2016" in their names that are located in Excel workbooks in subfolders of the "quarter_3" folder.
In the diagram below, the yellow highlighted item represents the current location, that is, the Excel workbook that you created a connection to in the "quarter_3" folder. The green box represents the tables belonging to workbooks and worksheets that are unioned as a result of this search criteria.
Include + Expand search to parent folder: If you enter *2016 in the Include text box and select the Expand search to parent folder check box, Tableau does the following:
Looks for all Excel worksheets that end with "2016" in their names inside the current folder, "quarter_3."
Looks for additional Excel worksheets that end with "2016" in their names that are located in parallel folders of the "quarter_3" folder. In this example, "quarter_4" is the parallel folder.
In the diagram below, the yellow highlighted item represents current location, that is, the Excel workbook that you created a connection to in the "quarter_3" folder. The green boxes represent the tables belonging to the workbook and worksheets that are unioned as a result of this search criteria.
- Include + Expand search to subfolders + Expand search to parent folder: If you enter *2016 in the Include text box and select both the Expand search to subfolders and Expand search to parent folder check boxes, Tableau does the following:
Looks for all Excel worksheets that end with "2016" in their namesinside the current folder, "quarter_3."
Looks for additional Excel workbooks that are located in the subfolders of the current folder, "quarter_3."
Looks for additional Excel workbooks that are located in parallel folders and subfolders of the "quarter_3" folder. In this example, "quarter_4" is the parallel folder.
In the diagram below, the yellow highlighted item represents the current location, that is, the Excel workbook that you created a connection to. The green box represents the tables belonging to the workbook and worksheets that are unioned as a result of this search criteria.
Note: When working with Excel data, wildcard search includes named ranges but excludes tables found by Data Interpreter.
No comments:
Post a Comment