You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 4 Next »

Purpose

This page purpose's is to introduce an Excel macro called "Quik2Code" with instructions to install and to provide use cases with examples.

Introduction

A research concept can be viewed as a collection of data elements with controlled vocabularies applied. At CDISC, we use the CDISC Controlled Terminology for identifying controlled vocabularies. To perform this task, a metadata developer often needed to cross-reference multiple spreadsheets; and, manually transcribe codelist contents by either re-typing or copy-and-pasting data. This mode of operation is not only tedious, but error-prone to typographical errors.

Quik2Code is designed to significantly reduce the manual intervention needed to create a research concept. It is an Excel macro with a user interface using data from the CDISC Controlled Terminology spreadsheet published quarterly. Use can make direct selection by checking off codelist values (controlled vocabularies) from a codelist. With click of a button, the macro will populate the selection in a predetermined format. Users will also be able to reverse lookup an existing list of controlled vocabularies. The tool will select codelist values found, as well as identify non-matching values that are either new vocabularies or typographical errors.

Installation

The following describes the steps to install Quik2Code on a Microsoft Windows PC:

StepsVisual Aid (Click image to enlarge)

A. Create folder and place Quik2Code Excel macro file

  1. Using File Explorer, on the C: drive, create a CDISC folder, i.e., C:\CDISC
  2. Place the Excel macro file quik2code.xlsm in it
 

B. Add CDISC folder as a trusted folder for launching Excel macro

  1. Open Excel
  2. Select File | Options. The Excel Options dialog box appears
  3. Select Trust Center on the left pane
  4. Click Trust Center Settings... The Trust Center dialog box appears
  5. Select Trusted Locations on the left pane
  6. Click Add new location... The Microsoft Office Trusted Location dialog box appears
  7. Type or browser to C:\CDISC as Path
  8. Click OK:
    • The Microsoft Office Trusted Location dialog box closes
    • C:\CDISC appears on the list of paths under Trusted Locations
  9. Click OK to close the Trust Center dialog box

C. Create a customize Excel ribbon for Quik2Code (assuming Excel 2010 and 2013 versions)

  1. Download this Excel UI customization file and place it into C:\CDISC folder
  2. Repeat Steps B1, B2 to bring back the Excel Options dialog box, if it is not already showing
  3.  Select Customize Ribbon
  4. Click Import/Export at the lower right corner of the Excel Options dialog box
  5. Select the Import customization file option. The File Open dialog box appears
  6. Browse to the Excel UI customization file saved in Step C1
  7. Click Yes when prompted to replace ribbon
    1. An entry labeled CDISC (Custom) appears on the right side of the Excel Options dialog box
    2. Expanding the node will reveal Function (Custom)
    3. Expanding again will reveal Quik2Code
  8. Click OK to close the Excel Options dialog box

D.Verify Quik2Code Install

  1. Select the ribbon labelled "CDISC" from the top menu
  2. Click the Quik2Code icon
    • The CDISC Controlled Codelist spreadsheet appears and may obstruct Quik2Code. If so, minimize the CDISC Controlled Codelist spreadsheet
  3. The Quik2Code's user interface appears

Use Case 1 - Direct Selection

Story: As a metadata developer, you want to pick out the commonly used codelist values from a codelist to constrain a research concept. The output will be a list of values with NCI EVS c-code, separated by semi-colon. To accomplish this using Quik2Code:

StepsVisual Aid (Click image to enlarge)
A. Launch Quik2Code from the CDISC ribbon 

B. Select a codelist from the top down down box

  • A list of values for the selected codelist appears
  • The codelists are listed using this nomenclature: <<CDISC Codelist Name>> - <<CDISC Submission Value>> for the codelist, followed by the <<Codelist Code>> in parenthesis,

For example: Age Unit - AGEU (C66781)


C. Check all the codelist values that apply 

D. Click the arrow button to the right will output a value list

For example, DAYS (C25301); MONTHS (C29846); YEARS (C29848) will display onto the active cell after selecting DAYS, MONTHS, and YEARS

 

 

Use Case 2 - Reverse Lookup

Story 1: As a metadata developer, you want to re-conform an existing value list which may not be in the desirable format.
Story 2: As a metadata developer, you want to verify the validity of all codelist values on a value list. You also want to know the invalid values so that you can make corrective action.

StepsVisual Aid (Click image to enlarge)
  
  
  
  • No labels