Creation date Minimum version
2017-9-22 1.2.2

Union All

The Union All extension is similar to the Union All operator in SQL. It concatenates two tables in the same workbook. (They can be from different worksheets.) The extension does not remove any duplicate rows.

Prerequisites

The tables concatenated by this extension must have the same schema, which means that the following prerequisites must be met:

  • The tables have the same number of columns.
  • The columns have matching data types.

    NOTE: Integer, Float, and Boolean are numeric types and are considered the same for the purpose of this extension.
  • The columns are ordered in the same way in both tables. (The only exception is the DATA column; its location has no effect on the extension.)
  • Neither table has a column of the Object or Array type. (The DATA column does not have to meet this requirement.)
  • Neither table has a blank, unpopulated column.

You might need to prepare the tables before applying this extension, for example, by changing the data types, hiding a column, or moving the columns within the worksheet.

Input

The extension takes two tables as input. The table name and column names of the resultant table are based on those of the first table. The following list describes the input you enter in the My Extensions panel:

  • By default, the table from which you start the extension is the first table. You can, however, click the down arrow to display a list of tables that exist in the active workbook. Click a table name from this list to select the table.
  • In the Bottom Table field, click the down arrow icon to display a list of tables in the active workbook. You can select any table in the list as the bottom table.
  • In the Final Table Name field, enter the name of the resultant table. By default, the resultant table name is in the following format:
  • firstTable_union_bottomTable

    For example, if the first table is named airlines1 and the bottom table is named airlines2, the extension creates the resultant table under the following name by default:

    airlines1_union_airlines2

Output

The resultant table is created in the active worksheet. The following list describes the characteristics of the resultant table:

  • If the original tables contain columns of different numeric types (for example, Integer, Float, and Boolean), the resultant column is assigned the Float type. For example, if the DayOfWeek_boolean column in the first table is Boolean and the DayOfWeek_float column in the bottom table is Float, the data type of the column in the resultant column is Float.
  • All columns are derived columns.
  • If the original column is a derived column, the name of the column in the resultant table is the same as that in the first table.
  • If the original column is a prefixed column, the name of the column is the resultant table retains the prefix.:

    For example, if the prefixed column in the first table is schedule1::duration, the column in the resultant table (which is a derived column) is schedule1_duration.

  • Because the extension does not remove any duplicate rows, the number of rows in the resultant table is equal to the sum of the rows in the two original tables.
  • The rows of the resultant table might be ordered differently than those in the original tables. For examples, rows from the bottom table might be displayed before rows from the first table.

Example

The following table is the first table from which you start the extension:

The following table is the bottom table that you can concatenate to the first table by using the extension:

n the My Extensions panel, select the tables to be concatenated as shown in the following partial screenshot:

The following table is the resultant table created by the extension:

 

Go to top