Incremental load using QVD files

[#1]

Hi,

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

Thanks in Advance.

[suniljain]

Pls check Attached QVW.

[#3]

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

[suniljain]

Sorry For Previous example.

But pls try attached example of increamental load

[#5]

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

[jansen28]

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

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

// 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