Versions Compared

Old Version 4

changes.mady.by.user Anthony Chow

Saved on

compared with

New Version Current

changes.mady.by.user Anthony Chow

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Info
titleNews

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.

Panel
titleLatest 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.

...

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
Image Modified

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
Image Modified

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

Image Modified

Image Modified

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
appears
  1. appear.
  2. Alternatively, enter a known codelist name into text area of the drop down box. Quik2Code will display a match, if available.
Note

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)

Image Modified

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.



Note
  • 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

Image Modified

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.
Note

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

active cell after selecting DAYS, MONTHS, and YEARS

destination cell

 Image Modified

...


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)
    
    • .
  2. Notice the two counters below the arrow button:
    • The upper counter shows # of matches.
    • The lower counter shows # of mismatches for each reverse lookup.

Image Added

C. Click output and review

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

Image Added

Additional examples and outputs:

Image Added