Important: Tutorial assumes the user is already familiar with Mapper, how to create mappings and mapping domains, and the general functionality of Mapper.

Note: The Consolidate Keys button was added to 23.3 or later which simplifies the steps following a Join Transformation by removing duplicate keys automatically.

Instructions

1. To begin transforming data, drag the first domain source onto the transformations layout, e.g. EDC-AE.
2. To filter the data, select Filter from the Variable drop-down menu, and update the expression with the appropriate logic, e.g. 'AESER' 'Actual Value' 'Equals' Value: 'Yes', click Add Condition and Save.
3. To add another domain to the listing, select Join from the Domain drop-down menu.
4. Drag the second domain source onto the empty domain in the join, e.g. Safety-SAELineListing.
5. Ensure the Join Type is Full, and update the Join Condition for the appropriate terms, e.g. Filter1.AETERM = Safety-SAELineListing.AETERM and Filter1.SUBJECT = Safety.SAELineListing.SUBJID.
6. To process the variables for comparison, select Variables from the Variable drop-down menu, and under the Edit Variables property:
   1. Uncheck any variables that should not be included in the listing.
   2. Update the variable names to matching names, e.g. Adverse Event Term_Clinical and Adverse Event Term_SAFETY.
   3. Update the Data Type to ISO 8601 for any date variables.
7. To merge the variables into unified discrepancy columns, select Variables from the Variable drop-down menu, and under the Edit Variables property:
   1. Click the Add Derived Variable button to add a variable column for each does not exist or mismatch comparison, e.g. EDC_Subject_Does_Not_Exist or Seriousness_Mismatch.
   2. Select Click to Add and input appropriate the T-SQL Expression, e.g. case when SUBJECT is null them 'EDC Data Does not Exist' else null end or case when [Seriousness_CLINICAL]<>[Seriousness_SAFETY] then 'Seriousness mismatch' + 'I' else '' end.
   3. Repeat until all derived variables are added.
8. To condense the discrepancy columns in a single column, select Variables from the Variable drop-down menu, and under the Edit Variables property:
   1. Click the Add Derived Variable button to add a variable column for Match Status.
   2. Select Click to Add and input appropriate the T-SQL Expression, e.g. case when (len(EDC_Subject_Not_Exists)>1) or (len(Seriousness_Mismatch)>1) then 'Mismatch' else 'Reconciled' end.
   3. Click the Add Derived Variable button to add a variable column for Query Text.
   4. Select Click to Add and input appropriate the T-SQL Expression, e.g. isnull(EDC_Subject_NotExists,'') + isnull(Seriousness_Mismatch,'').
   5. To hide the variable columns that were condensed so only the Mismatch Status and Query Text columns display in the listing, uncheck the others.
      

      Tip: It's best practice to hide those columns from view because they clutter the listing.

9. Add any additional transformations or apply comments to explain the logic used to create the listing for other users who may access the mapping.
10. To finish the reconciliation listing, select the Save button.