Mapping Keys for Import
There are scenarios where the 3rd party data may have different values for a Study, Site, Subject or Event in the data file that don’t match any EDC values, based on how the Study is configured in EDC. Instead of having the data vendor modify the data, which can be expensive or impossible, CDB supports the ability to map incoming values in a 3rd party data load to existing EDC data.
Prerequisites
Users with the standard CDMS Super User study role can perform the actions described below by default. If your organization uses custom Study Roles, your role must grant the following permissions %}
Type | Permission Label | Controls |
---|---|---|
Functional Permission | Workbench Tab | Ability to access and use the Data Workbench application, via the Workbench tab |
Functional Permission | View Import | Ability to access the Import page |
Functional Permission | Manage Key Mappings | Ability to create and manage key mappings for import |
If your Study contains restricted data, you must have the Restricted Data Access permission to view it.
Learn more about Study Roles.
Mapping Types
There are four types, or levels, of key mapping: Study, Site, Subject, and Event. Workbench includes supplemental data in the ZIP file depending on the type of key mapping template. The supplemental data for each key mapping type is as follows:
- Study: A CSV file that lists the Study Name and the External ID of the study.
- Site: A CSV file that lists the Site Number and Site Name for all the Sites in the Study Environment.
- Subject: A CSV file that lists all the Subjects in the Study.
- Event:
- A CSV file that lists all the Event Definitions, based on the last export from EDC.
- A CSV file that lists all the Event Group Definitions, based on the last export from EDC.
Key maps are defined for a Study on a Source/Key Mapping Level, so it is possible to have up to four (4) different key mappings per Source. Any updates for a specified Source and Level combination replace existing mappings for the same Source and Level combination.
Accessing Key Mapping
You can access key mappings from the Import area of CDB. You can navigate to the Import area from the Navigation Drawer () or, after you select your Study, from the Study menu () on the Studies page.
Once you’ve entered the Import area, click
Key Mappings in the header to open the subtab.Columns
The following columns display in Import >
Key Mapping:Column | Description |
---|---|
Source | The Name of the Source for the key mapping. |
Key Mapping Type | The Type (level) of the key mapping. |
Status | The status of the mapping file upload. The possible statuses are Pending, Complete, and Error. |
Uploaded | The date and time that the mapping file was uploaded (UTC). |
Create the Mapping File
To download a template:
- Navigate to Import > Key Mapping.
- Click Key Map Template.
- Select a Template.
Study
To create a Study mapping:
- For Mapping Type, enter “name” or “external_id”, based on which value you want to match against.
- For Study, enter the Study Name or External ID as it exists in Workbench (listed in the Study_Definitions.csv file).
- For Incoming Value, enter the value for Study as it exists in the import package.
Create additional rows for each possible key value pair.
The table below is an example Study_mapping_keys.csv file:
Mapping Type | Study | Incoming Value |
---|---|---|
name | Cholecap | Cholecap_Protocol1 |
Site
To create a Site mapping:
- For Mapping Type, enter either “name” or “number”, based on which value you want to match against.
- For Site, enter the Site Name or Study Site Number (based on your selection for Mapping Type) as it exists in Workbench (listed in the Sites.csv file).
- For Incoming Value, enter the value for Site as it exists in the import package.
Create additional rows for each possible key value pair.
The table below is an example Site_mapping_keys.csv file:
Mapping Type | Site | Incoming Value |
---|---|---|
name | Pleasanton General Hospital West | PGH-W |
name | Pleasanton General Hospital East | PGH-E |
Subject
To create a Subject mapping:
- For Subject, enter the Subject ID as it exists in Workbench.
- For Incoming Value, enter the Subject ID as it exists in the import package.
Create additional rows for each possible key value pair.
The table below is an example Subject_mapping_keys.csv file:
Subject | Incoming Value |
---|---|
0101-10001 | 101-10001 |
0101-10002 | 101-10002 |
0101-10003 | 101-10003 |
0102-10001 | 102-10001 |
0102-10002 | 102-10002 |
Event
To create an Event mapping:
- For Mapping Type, enter “name” or “external_id”, based on which value you want to match against.
- For Event, enter the Event Name or External ID as it exists in Workbench.
- For Event Sequence, enter the Sequence Number as it exists in Workbench.
- For Incoming Value, enter the Event as it exists in the import package.
Create additional rows for each possible key value pair.
The table below is an example Event_mapping_keys.csv file:
Mapping Type | Event | Event Sequence | Incoming Value |
---|---|---|---|
name | Visit_1 | 1 | Visit_1 |
name | Visit_2 | 1 | Visit_2 |
name | Visit_3 | 1 | Visit_3 |
name | Visit_1 | 2 | Visit_4 |
name | Visit_2 | 2 | Visit_5 |
name | Visit_3 | 2 | Visit_6 |
Import the Mapping
To import a key mapping file:
- Navigate to Import > Key Mapping.
- Drag and drop a file into the Choose a File (CSV) area.
- Click Import.
Import Statuses
Workbench tracks key mapping imports using these three (3) statuses:
Status | Description |
---|---|
Pending | Import is incomplete. |
Complete | Import finished successfully. |
Error | Import did not finish due to one or more errors. |
Errors
If there were any errors during import, you can view them from the Errors panel.
Study
The table below lists possible errors for study key mappings:
Message | Explanation | Resolution |
---|---|---|
One or more required column headers “Mapping Type”, “Study”, and “Incoming Value” are missing. | Your mapping CSV file is missing one of the required columns, or the CSV file has a header with the wrong label. | Update your CSV file to have the “Mapping Type”, “Study”, and “Incoming Value” columns with those exact labels. |
File contains columns beside “Mapping Type”, “Study”, and “Incoming Value”. | Your mapping CSV should have only three columns: Mapping Type, Study, and Incoming Value. | Remove any columns other than Mapping Type, Study, and Incoming Value. |
File contains invalid “Mapping Type” values. Value must be either “External_ID” or “Name” | Your Mapping Type column contains an invalid value. | Update the values in your Mapping Type column to use only “External_ID” or “Name”. |
File contains mixed “Mapping Type” values. Value must either all be “External_ID” or “Name”. | You can only use one Mapping Type per key mapping file. | Update the mappings so that you only use External_ID or Name for mapping type instead of both. |
File does not contain any records. | Your mapping file doesn’t have any mapping rows. | Enter mappings into your file. |
File contains multiple rows where the “Incoming Value” are the same. | Your mapping file contains one or more rows with the same value for Incoming Value. | Remove the duplicate rows or update the Incoming Value column so that there are no duplicates. |
Site
The table below lists possible errors for site key mappings:
Message | Explanation | Resolution |
---|---|---|
One or more required column headers “Mapping Type”, “Site”, and “Incoming Value” are missing. | Your mapping CSV file is missing one of the required columns, or the CSV file has a header with the wrong label. | Update your CSV file to have the “Mapping Type”, “Site”, and “Incoming Value” columns with those exact labels. |
File contains columns beside “Mapping Type”, “Site”, and “Incoming Value”. | Your mapping CSV should have only three columns: Mapping Type, Site, and Incoming Value. | Remove any columns other than Mapping Type, Site, and Incoming Value. |
File contains invalid “Mapping Type” values. Value must either be “Name” or “Number”. | Your Mapping Type column contains an invalid value. | Update the values in your Mapping Type column to use only “Name” or “Number”. |
File contains mixed “Mapping Type” values. Value must either all be “Name” or “Number”. | You can only use one Mapping Type per key mapping file. | Update the mappings so that you only use Number or Name for mapping type instead of both. |
File does not contain any records. | Your mapping file doesn’t have any mapping rows. | Enter mappings into your file. |
File contains multiple rows where the “Incoming Value” are the same. | Your mapping file contains one or more rows with the same value for Incoming Value. | Remove the duplicate rows or update the Incoming Value column so that there are no duplicates. |
Subject
The table below lists possible errors for subject key mappings:
Message | Explanation | Resolution |
---|---|---|
One or more required column headers “Subject” and “Incoming Value” are missing. | Your mapping CSV file is missing one of the required columns, or the CSV file has a header with the wrong label. | Update your CSV file to have the “Subject” and “Incoming Value” columns with those exact labels. |
File contains columns beside “Subject” and “Incoming Value”. | Your mapping CSV should have only two columns: Subject and Incoming Value. | Remove any columns other than Subject and Incoming Value. |
File does not contain any records. | Your mapping file doesn’t have any mapping rows. | Enter mappings into your file. |
File contains multiple rows where the “Incoming Value” are the same. | Your mapping file contains one or more rows with the same value for Incoming Value. | Remove the duplicate rows or update the Incoming Value column so that there are no duplicates. |
Event
The table below lists possible errors for event key mappings:
Message | Explanation | Resolution |
---|---|---|
One or more required column headers “Mapping Type”, “Event”, “Event Sequence”, and “Incoming Value” are missing. | Your mapping CSV file is missing one of the required columns, or the CSV file has a header with the wrong label. | Update your CSV file to have the “Mapping Type”, “Event”, “Event Sequence”, and “Incoming Value” columns with those exact labels. |
File contains columns beside “Mapping Type”, “Event”, “Event Sequence”, and “Incoming Value”. | Your mapping CSV should have only four columns: Mapping Type, Event, Event Sequence, and Incoming Value. | Remove any columns other than Mapping Type, Event, Event Sequence, and Incoming Value. |
File contains invalid “Mapping Type” values. Value must either be “External_ID” or “Name”. | Your Mapping Type column contains an invalid value. | Update the values in your Mapping Type column to use only “Name” or “External_ID”. |
File contains mixed “Mapping Type” values. Value must either all be “External_ID” or “Name”. | You can only use one Mapping Type per key mapping file. | Update the mappings so that you only use External_ID or Name for mapping type instead of both. |
File contains invalid “Event Sequence” values. Value must be an integer greater than 0. | The Event Sequence column requires an integer greater than 0. | Update the Event Sequence column to only contain integers greater than 0. |
File does not contain any records. | Your mapping file doesn’t have any mapping rows. | Enter mappings into your file. |
File contains multiple rows where the “Incoming Value” are the same. | Your mapping file contains one or more rows with the same value for Incoming Value. | Remove the duplicate rows or update the Incoming Value column so that there are no duplicates. |
Download a Mapping File
You can download current mapping files. To download a mapping file:
- Navigate to Import > Key Mapping.
- In the list, locate the Key Mapping that you want to download.
- From the Mapping menu (), select Download Mapping.
Delete a Mapping
You can delete mappings. If you delete a mapping, any future packages will be unable to import using those keys.
To delete a key mapping:
- Navigate to Import > Key Mapping.
- In the list, locate the Key Mapping that you want to delete.
- From the Mapping menu (), select Delete Mapping.
- Optional: In the confirmation dialog, select Do not show this again to not require confirmation on future deletes.
- Click OK.