Follow @dporton

JustCode : Delete cases from the output file of a dms script

This script shows you how to delete a case in the OnNextCase Event in the output data file.

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 4th August 2009
' Delete cases from the output file of a dms script.
' ****************************************
InputDatasource(Input, "")
    ConnectionString = "Provider=mrOleDB.Provider.2; _
        Data Source=mrDataFileDsc; _
        Location=C:tempshort_drinks.ddf; _
        Initial Catalog=C:\temp\short_drinks.mdd"
    SelectQuery = "SELECT * FROM VDATA"
End InputDatasource

OutputDatasource(Output, "")
    ConnectionString = "Provider=mrOleDB.Provider.2; _
        Data Source=mrSAVDsc; _
        Location=C:\temp\Output.sav; _
        MR Init MDM DataSource Use=2; _
        MR Init Overwrite=1"
       MetaDataOutputName = "C:\temp\Output.MDD"
End OutputDatasource

Logging(MyLog, "")
    Group = "DMA"
    Alias = "datamgmt"
    FileSize = 500
    Path = "c:\temp"
End Logging

Event(OnNextCase, "")

    If Respondent.Serial > 10 Then
        dmgrJob.DropCurrentCase()
        dmgrLog.LogError_2(Ctext(Respondent.Serial) + " - Case Deleted")
    End If

End Event

JustCode : Delete Empty columns of data

This mrs script shows you how you can use mrs files to make and run a dms file that will strip out any columns that do not have any data in.

Step 1: Open the VQ file and make an MDD FILE.

Dim mrDSCs, mrMdsc, oDoc,sVQ

sVQ = "C:\temp\FILE"
    Set mrMdsc = createobject("mrdscreg.components")
    Set mrMDSC = mrMdsc["mrScDsc"]

    Set oDoc = mrMdsc.Metadata.Open(sVQ + ".vq")
        oDoc.Save(sVQ + ".mdd")
        oDoc.Close()

Set oDoc = Null

Step 2: Take the MDD file and loop the variable instances


Dim oMDM, oVar
Dim sSQL,oConnection,oRecordset

' Create the MDM object
Set oMDM = CreateObject("MDM.Document")
    oMDM.Open("C:\temp\FILE.mdd", , 1)

    For Each oVar in oMDM.Variables
        'if oVar.IsSystem = False then
            debug.Log("Question Name : " + oVar.FullName)

            ' Start code to check if the field has data in it.
            Set oConnection = CreateObject("ADODB.Connection")
                oConnection.ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrScDSC;Location=C:\temp\FILE.VQ;Initial Catalog=C:\temp\FILE.mdd"
                oConnection.Open()

            If ( oConnection.State = 1 ) Then
                Set oRecordset = CreateObject("ADODB.Recordset")
                    oRecordset.Open("SELECT count(respondent.serial) FROM VDATA WHERE " + oVar.FullName + " is not null" ,oConnection,3,1)
                    If ( oRecordset.EOF = true and oRecordset.BOF = true ) Then
                        sSQL = sSQL
                    ELSE
                        sSQL = sSQL + oVar.FullName + ","
                    End if
                Set oRecordset = Null

            End If

            oConnection.Close()
            Set oConnection = Null
    ' end if
Next

oMDM.Close()
sSQL = Left(sSQL,len(sSQL)-1)
debug.Log(sSQL)

Step 3: Create the dms file with the select statement in it.


Dim oFSO, oFile

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFile = oFSO.CreateTextFile("C:\temp\FILE.dms", True)


oFile.WriteLine("InputDatasource(Input, """")")
oFile.WriteLine("    ConnectionString = ""Provider=mrOleDB.Provider.2;Data Source=mrScDSC;Location=C:\temp\FILE.vq;Initial Catalog=C:\temp\FILE.mdd"" ")
oFile.WriteLine("    SelectQuery = ""SELECT " + sSQL + " FROM VDATA""")
oFile.WriteLine("End InputDatasource")
oFile.WriteLine("")
oFile.WriteLine("OutputDatasource(Output, """")")
oFile.WriteLine("    ConnectionString = ""Provider=mrOleDB.Provider.2;Data Source=mrSavDsc;Location=C:\temp\Final.sav""")
oFile.WriteLine("    MetaDataOutputName = ""C:\temp\Final.mdd""")
oFile.WriteLine("End OutputDatasource")

oFile.Close()

Set oFSO = Null

Step 4: Run the file


Dim oDMOMJob

Set oDMOMJob = CreateObject("DMOM.Job")

oDMOMJob.Load("C:\temp\FILE.dms", null)

oDMOMJob.Run()

Add Context , Populate & Strip HTML out

You would have recently seen in LinkedIn that Barb asked the following question. “When you add html code into your survey, it displays beautifully but then it displays as code in your tabs. Is there a way in a DMS to globally eliminate any HTML code?” and I pointed him to a post that had been written awhile ago. Also in my comment I mentioned that I had a script that would add a context to an MDD and then strip out the HTML and put just the text in it. This article shows you the code for that script.

Read more

De-dupe your data files

In this article I will show you one way that you could use to de-dupe your data collection datafiles. I am not sure how you would get duplicates in it in the first place , but let’s just assume you do have them. This example will use the Museum.ddf file in the DDL. This file & the ddf can be found here “.DDLDataData Collection File”

Read more

Q & A : Comments in your scripting

Recently we where asked how do you put comments in your scripts. This quick post shows us that you can have two types of comments. The first is the block comment. To create a block comment place a ‘! at the start of the bit you want to comment out and then a !’ at the end and you will see that you have commented out that section.

Routing(Web)
'!
    * Survey Description : Comments 
    * Last Updated       : 16th June 2011

!'

Q1.ask()

' **** End of Survey
End:

End Routing

The next type of comment you can have is a single line comment. To create a single line comment place a ‘ at the front of the line that you want to comment out.

Q & A: Merging Two Datasets

Recently we where asked the following question and thought it was a good one for our questions and answers section.

Q: In relation to our having two existing questionnaires with the same questions, I saw in the DDL documentation that a project database can be merged into a second project database. What software is used to do this and what happens when you have duplicate serial numbers ?

Read more

%d bloggers like this: