Sub-Routine for Incremental Load

    Introduction

    Incremental Load means that only new and modified records should be read from the data source. By default, every time the Qlikview executes the Reload procedure, all data are read from tables. In other hand, for some cases the Incremental Load is necessary to store historical data from data source, while the tables not retains changed data. Incremental Load isn't a feature that is possible to enable or disable from QlikView. In fact, it's a resource from script execution. There are three types of requirements for Incremental Loads. First, when new records are inserted in source table and it's not necessary to read all data again, just new records. Second, when records are inserted or modified. It's necessary to get only these records, not all data. Finally, when records are inserted, modified or deleted. In this case, new records and modified are read from data source while deleted is dropped from last reload. The following routine is usefull to execute all these requirements.

    Subroutines are defined at first scripts tab. Any instruction/commands in SUB...ENDSUB aren't executed when script is running. Only when a CALL statement is used calling a procedure name that all code is executed. To use Incremental Load is necessary saves the data in .QVD files. The following procedure is used for both, either save a .QVD file and to execute a Incremental Load.

     

    SET v_SaveQVD = 1;

    LET v_FolderTarget = 'Q:\SelfStudy\Incremental Load';

     

    SUB SaveQVD(pAlias, pQVD, pDropTable, pIncremental, pExists, pNumRec, pPk, pTableType)


    // Parâmetros: pAlias - Nome da Tabela de Origem em Memória carregada do DB.
    // pQVD - Nome do Arquivo .Qvd a ser gravado em disco.
    // pDropTable - Indicador de Exclusão da Tabela em Memória Após Gravar Qvd.
    // pIncremental - Tipo de Carga Incremental. Ver guia Incremental Load.
    // pExists - Indica se Arquivo .Qvd já existe no disco.
    // pNumRec - Número de Registros Afetados.
    // pPk - Chave primária da tabela. Apenas para carga tipo 3.
    // pTableType - Carga de Arquivo ou de Database. Apenas para carga tipo 3.

    Trace 'Iniciando Rotina de Gravação do Qvd';

    Trace pExists: $(pExists);

    IF pExists = -1 and pNumRec > 0 THEN

    Trace 'Identificado Arquivo Qvd Existente';

    SWITCH $(pIncremental)

    CASE 1

    Trace 'Incremental Load for New Records in Progress...';

    $(pQVD):

    CONCATENATE ($(pAlias)) LOAD * FROM $(v_FolderTarget)\$(pQVD).QVD (QVD);

    CASE 2

    Trace 'Incremental Load for New/Updated Records in Progress...';

    $(pQVD):

    CONCATENATE ($(pAlias)) LOAD * FROM $(v_FolderTarget)\$(pQVD).QVD (QVD) WHERE NOT Exists ($(pPk));

    CASE 3

    Trace 'Incremental Load for New/Updated/Deleted Records in Progress...';

    CONCATENATE ($(pAlias)) LOAD * FROM $(v_FolderTarget)\$(pQVD).QVD (QVD) WHERE NOT Exists ($(pPk));

    IF pTableType = 1 THEN

    INNER JOIN LOAD $(pPk) FROM [$(v_FolderTarget)\$(pQVD).xls] (biff, embedded labels, table is $(pQVD)$);

    ELSEIF pTableType = 2 THEN

    INNER JOIN SQL SELECT $(pPk) FROM $(pAlias);

    ELSE

    SET v_SaveQVD = 0;

    ENDIF;

    END SWITCH;

    ENDIF;

     

    IF v_SaveQVD = 1 and pNumRec > 0 THEN

    Trace 'Saving QVD File... Wait!';

    STORE $(pAlias) INTO $(v_FolderTarget)\$(pQVD).QVD (QVD);

    ENDIF;

     

    IF pDropTable = 1 THEN

    DROP TABLE $(pAlias);

    ENDIF;

     

    END SUB;

     

    Pay attention for each parameter:

    • pAlias is the name for table loaded in memory. That mean, any name like Customers: that is used before LOAD/SELECT statement.
    • pQVD is a QVD filename that is going to be created. Your choice.
    • pDropTable is a boolean flag to indicate if a memory table should be dropped. Valid values are 0 or 1.
    • pIncremental indicates what type of Incremental Load wants to do. 1 = Insert, 2 = Insert and Update, 3 Insert, Update and Delete.
    • pExists is a boolean to indicate if a previous QVD file exists. If not, a full load was executed.
    • pNumRec test how many records was affected. If is zero, no new/updated/deleted records was found. So, re-save QVD is not necessary.
    • pPk is used only for pIncremtal 3. It's primary key for the table.
    • pTableType indicates if a source table is a Excel file or a relational database table. Only for pIncremtal 3.


    Variables that are defined at script level:

    • v_SaveQVD, when 1 indicates that a .QVD file should be created.
    • v_FolderTarget, indicates where .QVD files will be created.

     

    Calling the Procedure (Sub-Routine)

    To call a procedure using QlikView script, just use CALL instruction followed by procedure name and its parameters. The following code is a sample how to execute the procedure. First all, is necessary save when the script ran. That's done using vStart variable. After that, is necessary to test if a previous .QVD file exists, saving this information in vFileExists. vCriterio is the criteria used.

    LET vStart = Now(); 
    LET vFileExists = IF(FileSize('Qknow - Customers - v1.0.QVD') > 0, -1, 0); 
    LET vCriterio = IF(IsNull(vCriterio), 0, vCriterio); 

    After that, just load the data from datasource using the variable as criteria. Of course, in first load all data will be loaded because vCriterio is going to be zero. This variable will be updated at scripts end.Remember! In tables source is necessary exists a field to save information about when a record was inserted or updated. In our case is the InsertDate field.

    SourceTable: 
    LOAD CustomerID, CompanyName, CustomerName, Address, City, State, ZipCode, Country, Phone, Fax, InsertDate, ChangeDate FROM [qknow_customers_v1.0.xls] (biff, embedded labels, table is Clientes$) WHERE InsertDate > '$(vCriterio)'; 

    Now, the CALL statement can be executed using the following parameters:

    CALL SaveQVD ('SourceTable', 'Customers', 0, 1, $(v_FileExists), NoOfRows('SourceTable'), 'CustomerID', 1); 


    Remember!

    • SourceTable is a table name in memory.
    • Customers is the name for .QVD file.
    • Next parameter is zero to indicate the table will be in memory.
    • The number 1 is pIncremental parameter, indicating just new records are expected.
    • $(v_FileExists) is a variable to test if a previous file exists.
    • NoOfRows('SourceTable') tests how many rows was returned from data source.
    • Last two parameters are PK and source type. These has no effect when pIncremental is 1.

     

    At end, is necessary update the criteria.

    LET vCriterio = vStart;

     

     

     

    Need more?

    www.qknow.com.br