Page History

...

1. Download the CDE match template. The filetype should be XLSM. (Start with a fresh template for each import.) 

2. When opening your copy of the template, select Enable Macros. 

3. Populate your file with information about the CDEs that you want to match.

   1. Prepare data for loading in :
      1. Click the Source tab
      :
      
      1. . 

      2. In the first row, enter Row 1: Enter the text in each column to be used for matching to existing CDEs in CDE Match.  You may use any character as a column name except single quote ('). 

      3. In subsequent rows, enter Rows 2-N: Enter the permissible values or the value label, one per row below the column heading for each enumerated value. If the data element is non-enumerated, leave blank the cells below Row 1 for that column. If the permissible value is a local code (such as “1” or “2184”), enter the label/term for the code instead (such as “male”). 

      4. After filling in your column headings and permissible values, press Ctrl+t to transform content into the correct format for CDE Match. The macro creates a new Transform sheet containing one row for each unique combination of heading and permissible value. Columns with no rows beneath the header row on the Source sheet will only have one row on the Transform sheet. 

   2. Review the transformed data and revise for loading, if necessary. Click the Transform tab. The following table provides instructions for each column in the Transform tabthat sheet:

      Column Name	Instructions
      Batch User	Enter a batch user name in each row. You can provide any text in this column. The system does not validate this column.
      Batch Name	Enter a batch name in each row. The combination of Batch User and Batch Name must be unique. All rows in your file may have the same combination, but that combination must not match any other combination already in caDSR.
      Batch Sequence	The macro specifies a unique sequence ID for each data element (each column from the Source sheet). Change "Batch Sequence" to "Seq ID" in the column heading. (The Transform macro creates "Batch Sequence" column but the caDSR mapping file expects "Seq ID".)
      Entity	The macro specifies the name of the data element from the Source sheet.
      Perm Val	The macro specifies the permissible value from the Source sheet.
      User Tip	Enter a term or a phrase for the data element. CDE Match will use this term or phrase instead of the column heading from the Source sheet. The whole phrase is used, the system does not parse the words. This is useful if the column heading from the Source sheet is abbreviated or unclear.  For each data element that has multiple permissible values, only the first row for that data element needs a User Tip.
      Contexts	Enter a Context name to restrict the search for CDEs to only that Context.
      Preferred CDE ID	If you already know which CDE you prefer to use for this data element, enter the CDE Public ID and Version on the first row for the data element. If you enter a Preferred CDE, the system does not try to match to other CDEs.
      Preferred CDE Version	If you enter the CDE Public ID, also enter the Preferred CDE Version.
      Comments	Ignore.

      As you populate your file, keep in mind the following points:

      • You may add additional columns to the Transform tab for taking/keeping notes after the last template column, but the column names must be unique.

      • You can use your Delete key to clear the contents of one or more individual cells, but do not delete entire rows or columns. 

4. Use the Microsoft Excel "Save As" feature to save the Transform sheet as CSV. A message appears indicating that the CSV file type does not support multiple sheets. Click Click OK to save only the active sheet.  
5. Import the file into OneData:
   1. Log in as described in Logging In.
   2. From the Manage menu, select Manage Data. The Manage Data page appears. Select CDE Match. The CDE Match page appears. (For instructions on adding this page to your favorites, refer to Managing Favorites.)
   3. From the Import menu, select Conceptual Object Import. 
   4. Select the DS Import row and click Import (between Add New Mapping and Go To Data Manager). 
   5. Under Source Information, in the Import from File row, click Choose File. Navigate to and select your CSV file from your desktop. 
   6. Click Import at the bottom of the page. The system displays a message with the status of your import. 
6. Generate a list of CDEs that match your file:
   1. From the Manage menu, select Manage Data. The Manage Data page appears. Select CDE Match. The CDE Match page appears.
   2. Specify search criteria, such as all or part of your Batch Name. The format for the Date Last Modified field is MM/DD/YY (such as 03/05/22). 
   3. Click Apply Filter. Search results appear. 
   4. Select the checkbox for the rows you want to match. Select Run CDE Match. The system starts checking starts checking all the input columns from your transform sheet against the caDSR CDEs for possible matches, including comparing the values from your transform sheet with permitted values for the CDEs. The process can take a while to run. The system begins to fill in the number of PVs and the number of matched CDEs for each column in the input file. 
   5. You can navigate away from CDE Match and come back later to check whether it’s finished running. You can refresh the list of results by clicking Display Values.
7. Review the CDEs that match your file. The following table describes the columns in the list of values: 

   Column Name	Description
   Batch User	The Batch User

   entered batch user name can be used to filter results after importing in caDSR II UIBatch NameUnique batch name – can be used to filter results after importing in caDSR II UISeq IDAssigned by the Transform macro to each column in the import templateDate Last Modified (e.g. 05/01/22)The date CDE Match was run for this Seq ID/row.Column NameUser entered column name

   name from your input file. You can use it to sort or filter results.
   Batch Name	The Batch Name from your input file. You can use it to sort or filter results.
   Seq ID	The Seq ID from your input file. When you ran the Transform macro, it had created the Seq ID column with a number representing each column in the Source tab of the import template. You can use the Seq ID to sort or filter results.
   Date Last Modified	The date you (or another user) ran CDE Match for this row.
   Column Name	The column names from the Source tab in your copy of the template file. When attempting to find a matching CDE, the system inspects the CDE fields: CDE Long Name

   ,

   Question Text(s)

   ,

   Alternate Name(s)

   , and Like Long Name See diagram below for the order in which the match steps are processed.

   Like Long Name   Any character is permitted in the Column Name except single quote
   User Tip	A phrase or term entered for the column, there will be only one entry for the 1st row in the transformed spreadsheet if the column is enumerated. The system does not parse the phrase into individual terms. This phrase can be edited or removed in the UI after importing. The system will use the text in User Tip INSTEAD of the column name . Can be edited for each Column imported CDE after importing. Any character is permitted except single quote
   # Source PVs	Number of PVs  or Labels into the Source Spreadsheet. These are used as a second level of matching after matching the CDE by Column name or User Tips text. Only the best matching CDEs are compared to the source PVs/Labels. See diagram below for the order in which the match steps are processed.
   # CDEs Matched	The number of CDEs that were identified as possibly matching the column
   Preferred CDE Name	This will initially be blank unless a Preferred CDE was entered in the input file. After selecting “Set as Preferred” during the review process this column will contain the CDE name will be in this column.
   Preferred CDE ID	The ID of the CDE in the input template, or the ID selected manually as a match.
   Preferred CDE Version	The Version of the CDE in the input template, or the ID selected manually as a match.
   Contexts	One or more Context names separated that the match is restricted to. Can be edited after importing.
   Comments	Any comments entered by the end user

8. (TBD)
9. CDE Match has a command "Run DEC Match". This will run the match algorithm on the imported files using just the CDE names, ignoring the permissible values. Click the "Matched DEC" node to see results.

...