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

    Value Lists

    EricSean

      Title

      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
      uggs

        • 1. Re: Value Lists
          philmodjunk

          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" ]
          Loop
             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.