Script for Incremental Loading of Multiple QVDs from a List of Table Names

    Below is a script that was developed to quickly pull the fields from a list of tables into QVDs and perform incremental loads on the QVDs going forward.  This particulate script was created to pull data from Saleforce.com however it can easily be adapted for other sources as well.  The script depends on the primary key having the same name between all tables.  Instructions for using the script are included in the comments.

     

     

    //  Commets with ************ indicate locations where the script needs to be customized for your use



    //************Add Connection String below *************************


    //**********************************************************************
    Set vQVDPath = QVDs/; //****************  Enter Location to save QVDs in the path should end it back slash.  For example: C:\MyQVDs\
                                                                                                                  //                                         For realtive paths are also possible.  For example   QVDs\  will put the QVDs in a subfolder from where the QVW is saved.

    //Set the variables so the last time of script execution is known
    SET vLastExecTime          = 0; // resetting vLastExecTime
    LET vLastExecTime          = timestamp(if(isnull(QvdCreateTime('$(vQVDPath)ReloadHistory.QVD')), 0, ConvertToLocalTime(QvdCreateTime('$(vQVDPath)ReloadHistory.QVD'), 'GMT', 1)), 'YYYY-MM-DD hh:mm:ss');
    LET vExecTime                  = timestamp(UTC(), 'YYYY-MM-DD hh:mm:ss');
    LET vTodaysDate     = today();


    //For the 1st reload, this section will be skipped.
    LET FirstReload = isnull(QvdCreateTime('$(vQVDPath)ReloadHistory.QVD'));

    if Not $(FirstReload) then
    // Read Reload History Data
    ReloadHistory:
    Load
    No,
    [Last Reload Ended],
    [Reload Started]
    FROM $(vQVDPath)ReloadHistory.qvd (qvd);

    end if

    ReloadHistory:
    LOAD
    RowNo() as No,
    '$(vLastExecTime)'
    as [Last Reload Ended],
    '$(vExecTime)'
    as [Reload Started]
    Autogenerate(1);

    //ReloadHistory.qvd will keep track of when the last reload occurred for incremental loading purposes. If you need to reload all the data then delete ReloadHistory.qvd from your file structure.
                    STORE * FROM ReloadHistory INTO $(vQVDPath)ReloadHistory.qvd;

    //*****************Add a list of tables to be loaded from your data source below.  Keep the first row "TableName".  This is the name of the field.
    TablesList:
    Load * Inline [
    TableName
    Account
    Opportunity
    ]
    ;

    For i = 0 to (NoOfRows('TablesList')-1);
    Let varTableName = Peek('TableName',$(i), 'TablesList');

    // Load Tables
                    $(varTableName)_SQL:
    SQL Select *
    FROM
    $(varTableName)
    WHERE LastModifiedDate >=
    $(vLastExecTime) and LastModifiedDate < $(vExecTime);   //***************************Change the "LastModifiedDate" to the name of the field for the last modified data time stamp in your tables.
                   
    // For the 1st reload, this section will be skipped.
                    if Not $(FirstReload) and not isnull(QvdCreateTime('$(vQVDPath)$(varTableName)_SQL.qvd')) then

    //CONCATENATE ($(varTableName))
    $(varTableName)_SQL:
    Load *
    FROM $(vQVDPath)$(varTableName)_SQL.qvd (qvd)
    WHERE NOT EXISTS(Id);   // *************************************** Change "Id" to the primary key for your tables (note all your tables must use the same name for the primary key for this script to work.
                   
    end if

    // If table exists then proceed to the next step
                    if NoOfRows('$(varTableName)_SQL') > 0 then

    STORE $(varTableName)_SQL INTO $(vQVDPath)$(varTableName)_SQL.qvd;

    DROP TABLE $(varTableName)_SQL;

    End if
    Next;

    Drop Table TablesList;