Creating a numeric derived variable
This example creates a dynamically derived numeric variable called TotalInGroup. It is simply the sum of three other numeric variables (adults, under16s, and under11s) that record the number of people in each group.
' The output metadata document (.mdd) file
#define OUTPUTMDM "C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\museum_dd_expression.mdd"
' The output case data (.ddf) file
#define OUTPUTDDF "C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\museum_dd_expression.ddf"
' Copy the museum.mdd and museum.ddf sample files so that
' the derived variables examples will not update the original files...
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile("C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection File\museum.mdd", _
OUTPUTMDM, True)
fso.CopyFile("C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection File\museum.ddf", _
OUTPUTDDF, True)
' Make sure that the read-only attributes are not set
Set f = fso.GetFile(OUTPUTMDM)
If f.Attributes.BitAnd(1) Then
f.Attributes = f.Attributes - 1
End If
Set f = fso.GetFile(OUTPUTDDF)
If f.Attributes.BitAnd(1) Then
f.Attributes = f.Attributes - 1
End If
Dim MyDocument, MyDynamicallyDerivedVariable
' Create the MDM object and open the .mdd file
' in read-write mode
Set MyDocument = CreateObject("MDM.Document")
MyDocument.Open(OUTPUTMDM, , MDMLib.openConstants.oREADWRITE)
' Add the derived variable to the MDM Document
Set MyDynamicallyDerivedVariable = _
MyDocument.CreateVariable("TotalInGroup", "Total In Group")
MyDocument.Fields.Add(MyDynamicallyDerivedVariable)
MyDynamicallyDerivedVariable.DataType = mr.Long
' For numeric variables there's only one expression, so use
' Expression and not Expressions
MyDynamicallyDerivedVariable.SourceType = _
MDMLib.SourceTypeConstants.sExpression
' Set up the expression - add the number of people in each age
' range to get the total
MyDynamicallyDerivedVariable.Expression = _
"adults + under16s + under11s"
' Save and close the MDM Document
MyDocument.Save()
MyDocument.Close()
The script first copies the Museum sample files (museum.mdd and museum.ddf) to files called museum_dd_expression.mdd and museum_dd_expression.ddf, which avoids updating the original sample files. (museum_dd_expression.ddf will be used in the next topic.)
After running this example code, you can use
DM Query (which comes with the UNICOM Intelligence Developer Documentation Library) to see this new dynamically derived variable in action. For step-by-step instructions on setting up DM Query to run the queries, see
How to run the example queries in DM Query using the museum sample, but make sure that you select
museum_dd_expression.mdd instead of
museum.mdd.
Now that you are running DM Query and have opened the modified .mdd file, you are ready to start. Enter this query in the SQL text box:
SELECT adults, under16s, under11s, TotalInGroup FROM vdata
DM Query returns four columns of data. Here are the results for the first five cases:
adults under16s under11s TotalInGroup
5 1 2 8
3 1 0 4
2 1 0 3
2 1 0 3
TotalInGroup is the sum of the three subgroups.
Although there is a certain amount of code required to set up the variable, the actual values returned by the new dynamically derived variable TotalInGroup are defined by a single line of mrScriptBasic in the example, namely:
Expression = "adults + under16s + under11s"
When defining a derived variable, you can create the expression using any expression that is supported by the UNICOM Intelligence Data Model, including expressions that use the UNICOM Intelligence Data Model’s extensible
UNICOM Intelligence Function Library. You must enclose literals in
" " (double quotation marks) when defining an expression for a derived variable and not in
' ' (single quotation marks) as you do in SQL syntax. For further information about the expressions supported by the UNICOM Intelligence Data Model, see
Expression evaluation.
See also