News

2018-12-26: A patch version 1.1.2 is released to use the 4Q2018 (2018-12-21) release of CDISC Controlled Terminology.

2015-04-01: A patch version 1.1.1 is released to use the 1Q2015 (2015-03-27) release of CDISC Controlled Terminology.

2015-03-25: New version 1.1 is released to include CDISC COA Controlled Terminology, along with a couple of bug fixes.

Latest Quik2Code

quik2code.xlsm

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

  1. Click the drop down box to show a list of available codelist. A list of values for the selected codelist appear.
  2. Alternatively, enter a known codelist name into text area of the drop down box. Quik2Code will display a match, if available.

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

  1. Click on a codelist value to select. A highlighted value will be included in the output.
  2. Click on a highlight to-deselect.



  • Selecting a codelist value will display its details in the light grey shaded area below.
  • Some codelist values are long. Use the horizontal bar to scroll and view the entire text.
  • Some codelists have many entries. Use the vertical bar to scroll and view the entire value set.

For example, select 3 entries from the Age Unit codelist: DAYS, MONTHS, and YEARS

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

  1. Select a destination cell where an output shall appear
  2. Click the arrow button on the right. An output will appear in the destination cell.

Semi-colon is used as delimiter to separate each value on the value list output.

For example, DAYS (C25301); MONTHS (C29846); YEARS (C29848) will display onto the destination cell

 


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)
A. Launch Quik2Code from the CDISC ribbon

B. Configure the options in the Reverse Lookup section

  1. For Codelist short name at column, enter the column (between "A" and "Z") where a codelist short name can be found. It will be used to cross-reference the value list.
  2. For Values at column, enter the column (between "A" and "Z") where a value list can be found. In other words, Values at columnx are members of the Codelist short name at column y.
  3. If any values may be delimited by commas, use the Treat commas as semi-colon option. Caution should be given when using this option, especially when many valid codelist values contain commas.
  4. Click Lookup:
    • When a lookup is successful, the matching codelist will be displayed on the codelist drop down box.
    • Each matching codelist value will appear selected.
    • Non-matching codelist values will be cached for output (see Step C below).
  5. Notice the two counters below the arrow button:
    • The upper counter shows # of matches.
    • The lower counter shows # of mismatches for each reverse lookup.

C. Click output and review

  1. Select a destination cell where an output shall appear
  2. Click the arrow button to output
  • Matching is case-insensitive
  • Matched values will appear in black.
  • Non-matching values will appear after the matched ones, in blue without c-code

Additional examples and outputs:

  • No labels