Example of a horizontal merge
This example is provided as a sample data management script file called
MergeHorizontal.dms, which is installed with the UNICOM Intelligence Developer Documentation Library. For more information, see
Samples.
The two InputDataSource sections both contain JoinKey parameters that specify that the data sources will be joined using the Respondent.Serial system variable. The JoinType parameter in the first InputDataSource section specifies that a full join is required. When the script is run, the two input UNICOM Intelligence Data Files will be merged into a single UNICOM Intelligence Data File called MergeHorizontalOutput.ddf.
InputDatasource(myMasterDataSource)
ConnectionString = " _
Provider = mrOleDB.Provider.2; _
Data Source=mrDataFileDsc; _
Location = C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection File\MergeHorizontalMaster.ddf; _
Initial Catalog = C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection File\MergeHorizontalMaster.mdd"
JoinKey = "Respondent.Serial"
JoinType = "Full"
End InputDatasource
InputDatasource(mySecondInputDataSource)
ConnectionString = " _
Provider = mrOleDB.Provider.2; _
Data Source=mrDataFileDsc; _
Location = C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection File\MergeHorizontalSecond.ddf; _
Initial Catalog = C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection File\MergeHorizontalSecond.mdd"
JoinKey = "Respondent.Serial"
End InputDatasource
OutputDatasource(myOutputDataSource)
ConnectionString = " _
Provider = mrOleDB.Provider.2; _
Data Source = mrDataFileDsc; _
Location = C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\MergeHorizontalOutput.ddf;"
MetaDataOutputName = "C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\MergeHorizontalOutput.mdd"
End OutputDatasource
The two input files are based on the Short Drinks sample data, which is installed with the UNICOM Intelligence Developer Documentation Library, and contain case data collected using version 5 of short_drinks.mdd. However, the values of Respondent.Serial in both files have been renumbered starting from 1. MergeHorizontalMaster.ddf contains case data for all variables except age and income and is missing respondents 12 and 19. MergeHorizontalSecond.ddf contains case data for the age and income variables but is missing respondents 2, 23 and 26.
If you run the above script and look at the output data, you will see that a case exists for all respondents. However, respondents 12 and 19 will contain data for only age and income, and respondents 2, 23 and 26 will be missing data for age and income.
If you change the value of JoinType to “Left” and rerun this script, you will see that respondents 12 and 19 are now missing from the output data because they did not exist in the master data source. If you then change the value of JoinType to “Inner” and rerun this script, you will see that respondents 2, 12, 19, 23 and 26 are now missing from the output data because they were not present in both input data sources.
See also