Incremental Load

Mon, 24 Dec 2012 15:27:49 GMT

Please anyone Explain Incremental Load?

please give me Example.................

Re: Incremental Load

neha_shirsath — Tue, 25 Dec 2012 05:18:21 GMT

Hello,

Plz, see the attachment hope it will help you.

Re: Incremental Load

Wed, 26 Dec 2012 16:11:29 GMT

HI Neha,

i have personal edition.

i cannot open any of the qvw files. Plz attach doc file...

Re: Incremental Load

neha_shirsath — Thu, 27 Dec 2012 05:37:40 GMT

Hii nagaraju,

Please refer this code for incremental Load.

/* Setup for incremental load. */

The following items are used multiple times in the script.

It's easiest to specify them in variables.

SET vQvdFile='EventsTable.qvd'; //?? The QVD filename

SET vTableName='myTable'; //?? The name of the QV table we are loading on the Data Load tab

SET vPK='EventId'; //?? Data Primary Key

// Set a variable indicating if the QVD exists or not. -1 is True, 0 is False

LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);

IF $(vQvdExists) THEN // QVD exists, we will do an incremental reload

maxdateTab:

// Get the max date from this QVD for use in incremental SELECT

LOAD max(ModDate) as maxdate //?? Set the name of the date or datetime field

FROM $(vQvdFile) (qvd);

//?? Date may need to be formatted to match the format expected by your SQL SELECT or LOAD statement.

//?? e.g., date(peek('maxdate'),'MMDDYYYY');

LET vIncrementalExpression = 'WHERE ModDate >=' & peek('maxdate');

DROP table maxdateTab;

ELSE // QVD does not exist

LET vIncrementalExpression = ''; // No QVD. Force full reload

//LET vIncrementalExpression = 'WHERE ModDate >=' & MakeDate(2005,01,01); //?? Could also set it to some floor date

END IF

Load the incremental changes or full reload.

This is a standard LOAD (or SQL SELECT) statement.

vIncrementalExpression is the WHERE clause create in the previous tab,

-------------------------------------------------------------------------------------------------------------------

/* Data Load */

Directory;

$(vTableName):

LOAD

EventId, // Primary Key

ModDate, // Row modification date

EventDate,

Description,

Status

FROM EventsTable.csv (ansi, txt, delimiter is ',', embedded labels, msq)

// If doing a SQL SELECT, the vIncrementalExpression would be part of the SQL SELECT statement.

$(vIncrementalExpression) // Include WHERE clause created in "Incremental Setup" tab

;

----------------------------------------------------------------------------------------------------------

/* Incremental Update*/

Update the QVD with changes.

Directory;

// If incremental reload was , load previous data and concatenate to data just read.

IF $(vQvdExists) THEN

// Use CONCATENATE in case we've added any new fields.

CONCATENATE ($(vTableName)) LOAD * FROM $(vQvdFile) (qvd)

WHERE NOT exists($(vPK)) // Load only QVD rows that were not already loaded in the data load.

;

END IF

Handle deleted rows. The entire set of Primary Keys from the source table

is INNER JOINed with the Primary Keys in the QV data table.

Any Key that does not exist in the source table will be deleted from the QV Table.

For a large source table, it may take a while to fetch all the Key values.

If you do not need to handle deleted rows, don't include this step,

INNER JOIN ($(vTableName))

LOAD $(vPK)

FROM EventsTable.csv (ansi, txt, delimiter is ',', embedded labels, msq)

;

Overwrite the QVD with the QV datatable.

STORE $(vTableName) INTO $(vQvdFile);

//DROP TABLE $(datatable); // If we don't need the table in this qvw, it's a good practice to drop large tables.

Re: Incremental Load

Thu, 27 Dec 2012 09:44:35 GMT

It's very helpful... thank you

Re: Incremental Load

Thu, 27 Dec 2012 09:50:24 GMT

use thie link

Qlikview Cookbook http://robwunderlich.com/downloads provides an working incremental load example.

Qlikview Components http://qlikviewcomponents.org will let you do the whole thing with a couple lines of code.http://qlikviewnotes.blogspot.com/2012/01/incremental-load-using-qlikview.html

Hope this helps you.

Re: Incremental Load

neha_shirsath — Thu, 27 Dec 2012 10:49:14 GMT

If it is Helpful then please mark it is as a helpful answer.

Re: Incremental Load

gilles_2g — Thu, 14 Mar 2013 19:18:05 GMT

Vishwaranjan,

extremely good stuff in that Cookbook. I noticed this discussion still not marked closed, so I marked both your post and the one from Neha as helpful

topic Re: Incremental Load in QlikView

Incremental Load

Re: Incremental Load

Re: Incremental Load

Re: Incremental Load

Re: Incremental Load

Re: Incremental Load

Re: Incremental Load

Re: Incremental Load