1 Reply Latest reply on Dec 28, 2010 9:18 AM by philmodjunk

    Value Lists



      Value Lists

      Your post

      I have built a database where most of the data is text data.  This option was chosen to increase ease with data entry.  Now I want to analyze this data under a data analysis software (SPSS).  Is there a simple way for me to convert my text data into numerical data.  For example, one of my variables, ethnicity, has options:  African American, Asian, Hispanic, and European.  I would like to convert African American to 1, Asian to 2, Hispanic to 3, and European to 4.  What are the best ways to convert text variables to numerical variables.

      ugg boots

        • 1. Re: Value Lists

          You'll need some kind of calculation or relationship to match text to number.

          If this is a single value field (no checkbox groups):

          Case ( ethnicity = "African American" ; 1 ;
                      ethnicity = "Asian" ; 2 ;
                      ethnicity = "Hispanic" ; 3 ;
                      // and so forth

          Or you can define a table with the numbers and text in paired fields and define a relationship such as:

          YourTable::Ethnicity = EthnicityValues::Ethnicity

          Then a reference to EthnicityValues::Value could return the matching number value for the selected ethnicity.

          Now that I think more about this, you might also be able to write a script that loops through the list of values for a given value list and returns a number matching the order of the value in the value list:

          Set Variable [$ValueList ; ValueListItems ( Get ( FieldName ) ; "YourFieldListName" ]
             Set Variable [$I ; $I + 1]
             If [ YourTable::Ethnicity = Get Value ( $ValueList ; $I ]  
                Set Field [ YourTable::EthnicityValue ; $I ]
          End IF
          Exit Loop If [ $I = ValueCount ( $ValueList )]
          End Loop

          The advantage to this last approach is that you could generalize the script to pass field and value list names to it in a script parameter so that you could use the same script to assign numerical values for all your value list formatted fields.

          I haven't discussed how to do this with multi-value fields such as an ethnicity field formatted with a check box group as you haven't indicated what numerical value should be returned if two or more options are selected in the check box group.

          Note: going forward, I'd use the related table approach so that numerical values can be assigned at the time the data entry takes place rather than a batched, post processing approach after the fact.