Sub-Rotine for Incremental Load
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 rotine 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
.QVDfile should be created.
- v_FolderTarget, indicates where
.QVDfiles will be created.
Calling the Procedure (Sub-Rotine)
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
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
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)';
CALL statement can be executed using the following parameters:
CALL SaveQVD ('SourceTable', 'Customers', 0, 1, $(v_FileExists), NoOfRows('SourceTable'), 'CustomerID', 1);
- SourceTable is a table name in memory.
- Customers is the name for
- 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;
Do you wanna test this proecedure? Download the source table here!
- SWITCH..END SWITCH
- INNER JOIN
Learn to use variables to replace hard expressions.
Variables can get parameters to change its formulas. Learn more here!
|Need more? Mail Us!|