Important: This 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 is available with 23.3 or later which simplifies the steps following the Join Transformation, but is not required to complete the steps seen in the video.

Instructions

1. To begin transforming data, drag the first domain source onto the transformations layout, e.g. Clinical.EX.
2. To simplify the listing, click the Edit Variables property:
   1. Uncheck all variables.
   2. Check the desired variables, e.g. project, Subject, SiteNumber, Instance Name, FolderName, DataPageName, RecordID, RecordPosition, EXSTDAT_RAW, EXREAS, and EXAE.
   3. Update the Data Type to ISO-8601 for any date variables.
   4. Click Save.
3. To filter the data, add a Filter Transformation - select Filter from the Variable drop-down menu, and update the expression with the appropriate logic, e.g. 'EXREAS' 'Actual Value' 'Equals' Value: 'Yes', click Add Condition and Save.
4. To add another domain to the listing, add a Join Transformation - select Join from the Domain drop-down menu.
5. Drag the second domain source onto the empty domain in the join, e.g. Clinical.AE.
6. To simplify the second domain, ensure the Domain is selected, click the Edit Variables property:
   1. Uncheck all variables.
   2. Check the desired variables, e.g. project, Subject, RecordId, RecordPosition,AETERM, AESTDAT_RAW, AEENDAT_RAW and AEACN.
   3. Update the Data Type to ISO-8601 for any date variables.
7. Select the Join and Edit the Join Condition. Ensure the Join Type is Left, and update the Join Condition for the appropriate terms, e.g. Filter1.Subject = Clinical.AE.Subject and Filter1.EXAE = Clinical.AE.RecordPosition.
   

   Tip: Click the Consolidate Keys button (available 23.3+) to remove duplicate keys automatically.

8. Click Save.
9. To modify the results to correspond to the set of variables desired in the listing, add a Variables Transformation - 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, e.g. any duplicate keys from the Join step - AE.project, AE.Subject, and AE.RecordPosition if the Consolidate Keys button was not used in the Join step.
   2. Click the Add Derived Variable button to check dates between the two domains, e.g. Date_Check.
   3. Select Click to Add and input appropriate the T-SQL Expression, e.g.

      CASE WHEN EX.EXSTDAT_RAW NOT BETWEEN AE.AESTDAT_RAW AND AE.AEENDAT_RAW THEN 1 else 0 end.

   4. Click OK and click Save.
10. To be able to generate queries, add a Variables Transformation - select Variables from the Variable drop-down menu, and under the Edit Variables property:
    1. Rename any variables required for queries, e.g. RecordId to SourceRecordId.
    2. Click the Add Derived Variable button to add a variable column for each does not exist or mismatch comparison, e.g. FormOID, ItemOID, Check_Description, and QueryText.
    3. Select Click to Add and input appropriate the T-SQL Expression, e.g.

       'EX'

       'EX.EXREAS'

       'Review to ensure Dose Adjusted EX has corresponding AE #(s) on the Adverse Events page' 

       Case when aeterm is null then 'Dose Adjusted due to AE Log line :'+EXAE when aeacn=exreas then 'Dose Adjustment entered on both AE & EX' else 'Action Taken Reason of Adverse Event does not correspond with the identified AEs Action taken with Study Drug Please clarify or correct.' end

    4. Repeat until all derived variables are added.
    5. Click Save.
11. Add any additional transformations or apply comments to explain the logic used to create the listing for other users who may access the mapping.
12. To finish the reconciliation listing, select the Save button.