Incremental load using QVD files

Fri, 01 Oct 2010 10:43:53 GMT

Hi,

Can anybody provide the complete script example for performing incremenatl load using QVD.

Thanks in Advance.

Incremental load using QVD files

suniljain — Fri, 01 Oct 2010 10:52:35 GMT

Pls check Attached QVW.

Incremental load using QVD files

Fri, 01 Oct 2010 11:23:50 GMT

My datasource is locally store excel file can you please tell me how I can put where clause with last exce time.

Incremental load using QVD files

suniljain — Fri, 01 Oct 2010 11:50:25 GMT

Sorry For Previous example.

But pls try attached example of increamental load

Incremental load using QVD files

Fri, 01 Oct 2010 13:18:17 GMT

SEND ME ONLY SCRIPT AS I AM NOT ABLE OPEN THE QVW FILE.

Re: Incremental load using QVD files

jansen28 — Thu, 26 Apr 2012 23:56:59 GMT

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////

////////////////////////////////////////////////////////////////////

// Incremental logic for ADDITION of RECORDS in source only

// This logic doesnt cover UPDATIONs and DELETIONs in source

///////////////////////////////////////////////////////////////////

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////

// Step 0: You should always pick a "Change Data Capture" (CDC) Column and maintain it from the source to the target

// In our example, DATE_OF_SALE is the CDC column

// Step 1: Pick the Max(CDC Column) from the QVD file and store it in a one column, one row Resident table

// For initial loads, this will not work, so included a logic to pull full load for the first time

If (IsNull(FileSize('$(v_Home_Path)\Incremental load\Incremental.qvd'))) then

// Do nothing

else

MAX_DATE_TABLE:

LOAD Date#(max(DATE_OF_SALE),'MM/DD/YYYY') AS Max_date

FROM $(v_Home_Path)\Incremental load\Incremental.qvd(qvd);

ENDIF

// Step 2: Use the Peek function to pull in the Max date into a variable called the CDC Variable

// Use LET statement

IF(Isnull(v_Max_Date)) THEN

// Do nothing

ELSE

LET v_Max_Date = peek('Max_date',0,MAX_DATE_TABLE);

ENDIF

// Step 3: If the variable contains NULL, then it means that the QVD is empty, so pull the entire data from the source

// If the variable isn't NULL, then pull only those records from the source whose CDC column value is greater than the CDC Variable's value

// Store this data in a INCREMENTAL RESIDENT TABLE

// Concatenate this with the "Full Resident Load from the QVD"

// Now STORE the concatenated resident table into the QVD again.

IF (Isnull(v_Max_Date)) then

INITIAL_LOAD_SALE:

LOAD DATE_OF_SALE,

PRODUCT,

UNITS_SOLD

FROM

$(v_Home_Path)\Incremental load\Test Data.xlsx

(ooxml, embedded labels, table is Sheet1);

STORE INITIAL_LOAD_SALE INTO $(v_Home_Path)\Incremental load\Incremental.qvd;

else

INCREMENTAL_SALE:

LOAD DATE_OF_SALE,

PRODUCT,

UNITS_SOLD

FROM

$(v_Home_Path)\Incremental load\Test Data.xlsx

(ooxml, embedded labels, table is Sheet1)

WHERE DATE_OF_SALE > $(v_Max_Date);

FULL_SALE:

Concatenate (INCREMENTAL_SALE)

LOAD DATE_OF_SALE,

PRODUCT,

UNITS_SOLD

FROM

$(v_Home_Path)\Incremental load\Incremental.qvd

(qvd);

STORE INCREMENTAL_SALE INTO $(v_Home_Path)\Incremental load\Incremental.qvd;

ENDIF

topic Incremental load using QVD files in QlikView

Incremental load using QVD files

Incremental load using QVD files

Incremental load using QVD files

Incremental load using QVD files

Incremental load using QVD files

Re: Incremental load using QVD files