In addition to the variable transformation options, Mapper also supports domain transformations, including Join, Union, Subtract, and Intersect.

A Join merges multiple table structures, while Union, Subtract, and Intersect transformations require the source tables to share the same structure.

Important: Join, Union, Subtract, and Intersect transformations respect user privileges and configured Data Blinding rules. If the user or execution account has the Blinded Data View privilege, unblinded values may be included in transformation results and written to derived outputs. Verify that access privileges align with the study’s blinding strategy before creating or executing transformations that include sensitive data.

Table Joins

A table Join creates a new structure by joining the columns from multiple tables. Once the tables are joined, additional variable mapping can be performed to rename, reorder, filter, and select variables.

The result is a new table structure that includes all variables from both tables in the join. The specific data included is determined by the Join Type and Join Condition, described below.

Create a Joined Table

1. In the Domain Editor, select a domain.
2. Click the Domain drop-down and select Join. A Join box is added to the selected domain, and a warning displays in the preview area.
   
    
3. Drag the table to be joined with the original table into the ?? box on the right.
   
4. Ensure the Join box is selected. Dotted lines display around the box when selected. The Join Type and Join Condition fields are displayed in the Domain Properties subpanel.
   
    
5. Double-click the Join box or click the ellipsis (...) in the Join Condition field in the Domain Properties subpanel. The Edit Join Condition window opens.
   
    
6. Select the matching key fields to use for joining the tables. Typically, at least one field in the first table is = (equals operator) to a field in the second table. Common fields used for joins include SubjectID, Unique Subject ID or Visit Number.
7. Click Add + to add a field to the join condition.
8. Click the Consolidate Keys button to limit the results of the joined fields to a single field.
   • For Inner and Left joins, results include values from the left table only.
   • For a Right join, results include values from the right table only.
   • For a Full join, results include values from the left table if present; otherwise, values from the right table are included.
9. Select the Case Sensitive Joins checkbox to make text joins case sensitive.
10. Select additional join rules using operators such as <>, >, or <.
11. Click Save to save the join conditions.
12. In the Join Type drop-down, open the drop-down list and select one of the join types:

TYPE	EXAMPLE	DESCRIPTION	ICON
Left		Returns all rows from the left table in conjunction with the matching rows from the right table.
Right		Returns all rows from the right table in conjunction with the matching rows from the left table.
Inner		Returns records that match the condition in both tables.
Full		Returns rows from either table when the join condition is met.
Cross		Returns a result set equal to the number of rows in the first table multiplied by the number of rows in the second table, or, if a condition is set (WHERE clause) it acts as an INNER join.

13. After the Join Type is selected, the preview area displays the joined table structure.

Note: When joining tables that contain sensitive data, review Data Blinding rules and mapped domain blinding options carefully. Joined results can combine blinded and unblinded fields from both source tables, depending on user privileges, mapped domain blinding options, and Data Blinding configuration.

Note: Fields that exist in both tables (e.g., AE and DM) and are part of the join display in the results with a prefix based on the original table name. For example, if the PROJECT field exists in both tables, it displays as AE_PROJECT as well as DM_PROJECT.

Table Unions

The Union table mapping option enables creation of a single table from the data in two existing tables, allowing data from multiple sources to be combined.

For example, lab data is often separated into different tables by category. Lab results may exist in multiple tables such as LB_CHEM, LB_HEMA, and LB_URIN. Using the Union functionality, a unified lab table can be created.

If the two tables have the same number of fields, field order, and data types, the Union runs automatically without additional configuration. If they differ, the Sync Columns property must be used to select matching fields and target data types between the two tables.

Create a Union

1. In the Domain Editor window, select a domain.
2. Click the Domain drop-down and select Union. A Union box is added to the selected domain and a warning displays in the preview area.
3. Drag the second table containing the additional records into the Union box.

4. If the field counts and data types match, the Union results are previewed in the preview area.

   Note: When unioning tables that contain sensitive data, the resulting output respects the current user’s privileges and Data Blinding configuration. Review the output carefully to ensure sensitive values remain protected as expected.

   • If the field counts and data types do not match, use the Sync Columns property as described below.

   

   Note: If field data types differ, SQL Server attempts an implicit conversion. For example, a numeric field and a string field fail to merge if the string contains non-numeric characters (such as N/A or none).

If necessary, pivot, unpivot, or other variable transformations can be applied to the underlying tables. To modify one of the selected tables, click the table to highlight it and select the appropriate tool from the Variable or Domain drop-down.

When the field numbers and data types match, the Union results are displayed in the preview area.

Use the Sync Columns Tool for Unions

In cases where the field count and types do not match, the Preview area displays the following message. This indicates that the selected tables must be aligned before the transformation can run.

“All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.”

1. Double-click the Union box, or click the ellipsis (...) in the Sync Columns field in the Domain Properties subpanel. The Edit Union window opens.
   
2. Click Auto Match to attempt an automatic match between available columns. The system attempts to match columns by variable name and data type.
3. To manually match variables, select variables from the table on the left and drag them to the Drop Left Variables Here field.
4. When matching columns from the left and right tables, multiple variables can be selected using the Ctrl+Click or Shift+Click and dragged together.
   • Fields can also be inserted into the list by holding Shift while dragging.
   • When using Ctrl+Click or Shift+Click to add multiple variables at once, verify placement carefully, as the system does not automatically identify corresponding matches.
5. Select the corresponding variable on the right and drag it to the Drop Right Variables Here field.
6. Continue until all required variables are matched.
7. Click Save to save changes to the union.

When a match is made, selecting the variable in the center highlights the connected variables on the left and right sides.

Note the effect in the image below: 

Dynamic Unions

The Dynamic Union allows creation of a union that includes variables from multiple domains by searching for a specified set of variables across those domains. This enables combining data from multiple domains without adding multiple Union transformations.

For example, the Dynamic Union can be used to combine date fields that end with DTC or DAT from all clinical domains in a single transformation. The resulting data can then be joined to another table to verify that dates do not precede patient consent.

1. In the Domain Editor window, select a domain.
2. Click the Domain drop-down and select Dynamic Union.
   • A Union1 box is added to the Domain Editor window, and a warning displays in the preview area.
3. Ensure the Union1 box is selected and click the ellipsis (...) in the Union Config field in the Domain Properties subpanel. The Dynamic Union window opens.
   
4. In the Data Store field, click the drop-down and select the data store that contains the domains to search for variables.
5. In the Tables section do the following:
   1. Enter the search parameters to define which tables to search for matching fields. After selecting the type of search, enter the table specifications.
      • Starts With: Searches for tables that begin with the specified characters. For example, entering D selects tables such as DM, Demog, DA, and DV.
      • Ends With: Searches for tables that end with the specified characters. For example, entering M selects tables such as CM, DM, and LAB_CHEM.
      • Contains: Searches for tables containing a specific set of characters. For example, entering LAB selects tables such as LAB_CHEM, LAB_HEME, and NEW_LAB.
      • Wildcard: Searches using wildcards like * and ? to match variations in table names. Multiple matches can be included. For example, entering LAB*, LB* selects tables such as LAB_CHEM, LAB_HEME, LB1, and LB2.
   2. In the exclusions field, enter a comma-separated list of tables to exclude from the search. Wildcards can also be used. For example, entering supp* excludes tables that start with supp (commonly used for supplemental tables).
6. In the Fields section use the same search parameters described above (for searching tables) to locate specific fields. For example, to find all fields ending with DAT, select Ends With and enter DAT in the Field Specifications field. This selects fields such as AEDAT, AEENDAT, CMENDAT, and CMNSTDAT.
7. In the Data Type section, select the data type of the field. 
   • Only a single data type can be selected. If the fields use multiple data types, create separate unions for each type (for example, one dynamic union for DAT fields with Datetime and another for DAT fields with Text).
8. In the Key Fields section do the following:
   1. Enter a comma-separated list of fields to include in the output.
      • If these fields do not exist in all source tables, the corresponding values display as null.
   2. Select the Include table and field names in the output checkbox to include details about the source of the unioned values.
9. Click Save.

Note: Dynamic Union can combine matching fields across many domains. If those domains include blinded data, review the selected tables, fields, and resulting output carefully to confirm that Data Blinding is applied as intended.

Intersect and Subtract

Intersect and Subtract domain mappings operate similarly to a Union. All three domain mapping tools function automatically with no additional configuration if the two tables have the same number of fields. If not, use the Sync Columns property to align the required fields.

The Intersect function creates a result table that includes only records found in both mapped domains.

For example, to identify duplicate records in two tables, use the Intersect function. Only records that appear in both tables are included in the results.

The Subtract function creates a result table that includes only records found in the first table and not in the second.
This can be used, for instance, when comparing all data against reviewed data, where the result includes only unreviewed data.

Note: Intersect and Subtract results also respect user privileges and configured Data Blinding rules. When comparing tables that contain sensitive data, verify that the resulting output does not expose values that should remain blinded.

Create an Intersect

1. In the Domain Editor window, select a domain.
2. Click the Domain drop-down and select Intersect.
   • An Intersect box is added to the selected domain, and a warning displays in the preview area.
     
3. Drag the second table containing the records to compare.
   • The format and structure of both tables must match.

Note: If necessary, use Pivot, Unpivot, or other variable transformations to modify the underlying tables. To modify a selected table, click the table to highlight it and select the appropriate tool from the Variable or Domain drop-down.

If the field numbers and data types match, the Intersect results are displayed in the preview area.
If the field count or data types do not match, use the Sync Columns property as described above.