When I started learning to use OpenClinica, I was mystified by all of the things there were to learn. One year later, I continue to be surprised by the number of new skills and tricks I am discovering – whether in the form of existing functionality, new features (EventAction Rules!) or community contributions.
In May, I was putting together a workshop for OC14, OpenClinica’s Global Conference, on best practices for building studies in OpenClinica, and a colleague suggested I spend some time explaining all of the functions in our platform that can be used for calculations.
Alright, I thought, that should be easy. Functions for calculations in OpenClinica, while useful, are pretty simple. After reviewing the information found in the CRF Excel template for calculations, this is what I found:
Sum()? Got it.
Min() and max()? Easy.
Stdev()? Fine.
Decode()? … wait, what?
A vague memory of our trainer’s explanation of the decode() function at OpenClinica’s Central User Training flashed through my mind. My guess is that there are users out there who don’t even know this little gem exists. Some of you may have seen it and simply dismissed it.
So what exactly is the decode() function?
The decode() function allows you to create an IF-THEN-ELSE type of statement. An item is compared to a list of search values, one by one. If the item matches a search value, then a corresponding result value is returned.
Here is your basic format:
decode(ComparatorItem, SearchVal1, ResultVal1, SearchVal2, ResultVal2, … DefaultValue)
The ComparatorItem is compared to each SearchVal in the list, one at a time:
- The ComparatorItem is first compared to SearchVal1.
- If the ComparatorItem = SearchVal1, return ResultVal1.
- Otherwise, if ComparatorItem = SearchVal2, return Result2
- And so on…
- If none of the values in the Search List match the ComparatorItem, then the DefaultValue is returned.
- The DefaultValue is optional, and can be omitted entirely.
The decode() function is probably best explained using an example. I will start by illustrating the general format of the function, and then move on to a few examples that may prove useful in your OpenClinica studies.
Let’s say users are entering a cardinal direction initial (i.e. N, S, E, W) and you want to decode the initial to the actual cardinal direction name (i.e. North, South, East, West). Your decode function would resemble the following:
decode(DirectionInitial, N, North, S, South, E, East, W, West, Unknown)
Upon clicking save, the entry in the DirectionInitial Item will first be compared to “N” and if there is a match, “North” is returned. Otherwise, the DirectionInitial Item is compared to “E” and if there is a match, “East” is returned, and so on. If DirectionInitial Item does not match any of the options (N, S, E, or W), then “Unknown” will be returned.
Let’s look at a few examples that may be more applicable to your OpenClinica studies…
Temperature
If you have a Vital Signs or Physical Exam CRF, you may be capturing the Subject’s temperature. In the case report form, there can be two options for temperature units (Celsius or Fahrenheit), but the statistician may be interested only in a final temperature in degrees Celsius. To please both your data entry person and your statistician, you can create a field to capture the Temperature, and then create another Units field to record either Celsius or Fahrenheit. The final Temperature item will contain the decode function, which would resemble the following:
decode(TemperatureUnits, Celsius, Temperature, Fahrenheit, ((Temperature-32)*(5/9)) )
If TemperatureUnits is equal to “Celsius,” then return the value recorded in the Temperature Item. Otherwise, if TemperatureUnits is equal to “Fahrenheit,” then convert the value recorded in the Temperature Item to Celsius.
Scoring
Additionally, the decode() function is a great tool when scoring responses. For example, you may have an eligibility questionnaire with response options Yes, No, and NA, corresponding to response values 1, 0, and 99. You would like to count the number of ‘Yes’ responses. However, a simple sum() function will not work because if ‘NA’ is selected, ‘99’ will be included in the calculation. This can be solved by combining the decode() function with the sum() function.
First, you will need to determine the decode function for each item. In this case your function would be as follows:
decode(ItemName,1,1,0,0,99,0)
If Yes is selected (value of 1), then 1 will be returned. If No is selected (value of 0) then 0 will be returned. If NA is selected (value of 99) then 0 will be returned.
Note: We only want to calculate the number of ‘Yes’ responses.
This can then be combined into a sum() function. If you have three eligibility items in your CRF, then your decode function would be as follows:
sum(decode(Item1, 1,1,0,0,99,0), decode(Item2, 1,1,0,0,99,0), decode(Item3, 1,1,0,0,99,0)
Interested in seeing any of these examples in action?
You can download this CRF Excel template which includes each of the three above examples.
Also, if you found another useful way to use this function, feel free to comment and share your tips with us!