Professional > Data management scripting > Data cleaning > Data cleaning examples > Example 1: More than one response to a single response question
 
Example 1: More than one response to a single response question
Data cleaning shows that there are many possible solutions to cleaning data that contains multiple responses to single response questions. This topic provides some mrScriptBasic code for implementing some of these solutions.
Except where specified otherwise, you implement the mrScriptBasic code in the OnNextCase Event section of the DMS file. All of the variables that were included in the Select Query in the InputDataSource section are automatically available as Data Management Object Model (DMOM) Question objects in the OnNextCase section and you can access the responses using the Question.Response property.
To see the code as it would appear in a DMS file, see Example 2: A complete example.
Delete all responses and replace them with Not Answered
This example uses the AnswerCount function to return the number of responses the respondent chose when answering the question. If the result is more than 1, the response is then set to the Not answered category.
If interest.Response.AnswerCount() > 1 Then
  interest.Response = {Not_answered}
End If
You do not need to specify the Question.Response property explicitly because it is the default property for simple questions. This means you can simplify the code like this:
If interest.AnswerCount() > 1
  Then interest = {Not_answered}
End If
Delete the respondent record
This example also uses the AnswerCount function, but this time if there is more than one response, the DropCurrentCase method is called so that the case is not transferred to the output data source.
If gender.AnswerCount() > 1 Then
  dmgrJob.DropCurrentCase()
DropCurrentCase does not delete the case from the input data source--it merely drops the case from the transformation.
Select one response randomly
This example uses the Ran function to select one response randomly:
If time_spent.AnswerCount() > 1 Then
time_spent = time_spent.Response.ran(1)
End If
Mark the case as requiring review
This example adds a text to the DataCleaning.Note system variable and sets the DataCleaning.Status system variable to Needs review.
If age.AnswerCount() > 1 Then
  DataCleaning.Note = DataCleaning.Note + " Age needs checking."
  DataCleaning.Status = {NeedsReview}
End If
Write the details to a report
This example shows writing a report of the problem to a text file. The example shows the OnJobStart, OnNextCase, and OnJobEnd Event sections of the DMS file only.
The OnJobStart Event uses the CreateObject function to create a FileSystemObject, which is a standard Microsoft object for working with folders and files. The FileSystemObject.CreateTextFile method is then called to create a text file object, which is then added to the global variables collection.
The OnNextCase Event section sets up a string and calls the WriteLine method on the text file object to write it to the text file. The CText function has been used to convert the numeric serial number to text.
The OnJobEnd Event section closes the text file.
Event(OnJobStart, "Do the set up")
Dim fso, txtfile
Set fso = CreateObject("Scripting.FileSystemObject")
Set txtfile = fso.CreateTextFile("Cleaning.txt", True, True)
dmgrGlobal.Add("mytextfile")
Set dmgrGlobal.mytextfile = txtfile
expect.Response.Default = {general_knowledge_and_education}
End Event

Event(OnNextCase, "Check the data")
Dim strDetails
strDetails = CText(Respondent.Serial)

If age.AnswerCount() > 1 Then
DataCleaning.Note = DataCleaning.Note + " Age needs checking."
DataCleaning.Status = {NeedsReview}
strDetails = strDetails + " Age needs checking."
End If

dmgrGlobal.mytextfile.WriteLine(strDetails)
End Event

Event(OnJobEnd, "Close the text file")
dmgrGlobal.mytextfile.Close()
End Event
You could use a similar technique to write questionable responses to the text file or to write the information to a .csv file, which is easy to open in Excel. For an mrScriptBasic example of creating a .csv file, see 2: Getting a list of valid languages. Alternatively, you could write the report to the log file: see Logging section. In addition the MSWordReport.dms sample files shows how to create a report in Word.
Replace the responses with a predefined default
You can use the Response.Default property to set up a default response for a question. You can then use the Validation.Validate method to assign the default value to questions for which the respondent has selected more than one response.
Event(OnJobStart, "Set up the default")
expect.Response.Default = {general_knowledge_and_education}
End Event

Event(OnNextCase, "Clean the data")
 If expect.AnswerCount() > 1 Then
    expect.Validation.Validate(ValidateActions.vaAssignDefault)
  End If
End Event
The Validate method checks the maximum value set for the variable. This means that this code only replaces the respondent's selected responses with the default if he or she has chosen more responses than the maximum number allowed. The code works in this example because expect is a single response variable, which by definition means that its maximum value is set to 1. However, it is possible to change the maximum value in the script. For an example of doing this, see Example 3: More on cleaning single response data.
When you use a constant that is defined in a type library that is registered in the script, you must include the name of the type definition. For example, if this example had excluded the “ValidateActions” name and specified only “vaAssignDefault” as the parameter to the Validate method, mrScriptBasic would give an “Unidentified variable” error. For more information, see How does mrScriptBasic compare to Visual Basic?.
Requirements
UNICOM Intelligence Professional
See also
Data cleaning examples