Creating a union data set
A union data set combines the results returned by two or more data sets. Creating a union data set is similar to using a SQL UNION ALL statement, which combines the result sets of multiple SELECT statements into a single result set.
Create a union data set to consolidate data from multiple sources that have similar data structures. For example, a company uses separate database tables to store contact information about employees and contractors. The structures of the tables are similar. Both contain Name and Phone fields. Suppose you want to create a master contact list for all employees and contractors. The solution is to create one data set to retrieve employee data, a second data set to retrieve contractor data, and a union data set to combine data from the previous data sets.
Figure 2‑11 illustrates the data sets that return employee and contractor data.
Figure 2‑11 Data sets with common fields for employee and contractor data
When creating a union data set, you select the fields to include.
Figure 2‑12 shows a union data set that includes all the fields from both the Employees and Contractors data sets. The Name field contains all employee and contractor names. The Phone field also contains all employee and contractor phone numbers. The E-mail field exists only in the Employees data set, so only employee data rows have e-mail data.
Figure 2‑12 Union data set of all data from Employees and Contractors data sets
Figure 2‑13 shows a union data set that includes only the common fields, Name and Phone, from the Employees and Contractors data sets.
Figure 2‑13 Union data set of common fields in Employees and Contractors data sets
In the previous example, the two data sets used to create a union data set contained common fields with the same names. This condition is required for consolidating data into a single field. However, data sources often use different field names. Suppose the Name field in the Employees and Contractors data sets is EmployeeName and ContractorName, respectively. To create a union data set that consolidates employee and contractor names in a single field, rename the field names in the individual data sets to use the same name. When creating the Employees data set, in Output Columns, use the Alias property to give the EmployeeName field another name.
Figure 2‑14 shows the EmployeeName field with the alias, Name.
Figure 2‑14 Alias specified for the EmployeeName field
Similarly, when creating the Contractors data set, edit the ContractorName field to use the same alias.
How to create a union data set
This procedure assumes that you have created the data sets to include in the union data set.
1 In Data Explorer, right-click Data Sets, and choose Union Data Set.
2 In New Data Set, in Data Set Name, optionally type a name for the union data set.
3 Choose New.
4 In New Union Element, in Select Data Set, select the first data set that contains the data to include in the union data set.
The fields in the selected data set appear, as shown in
Figure 2‑15.
Figure 2‑15 Fields in a data set selected for a union data set
5 Select the fields to include in the union data set, then choose OK.
6 Repeat steps
3 to
5 to add the next data set to the union data set.
Figure 2‑16 shows a union data set named Master Customer List that consists of fields from two data sets, PlatinumCustomers and GoldCustomers.
Figure 2‑16 Definition of a union data set that combines two data sets
Choose Finish. Edit Data Set displays the selected fields, and provides options for editing the data set.
7 Choose Preview Results.
Figure 2‑17 shows the rows returned by the Master Customer List union data set.
Figure 2‑17 Data rows returned by the union data set
