Creating Incremental Load from Cache Database

[#1]

I am trying to create an incremental load from a Cache database.

Here is what I have so far -

Main :

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

set VSourceData = 'C:\Qlikview\Production\Data Echosystem\QVDs';

set VSaveQVD = 'C:\Qlikview\Production\QvIncludes';

Let vQVDCreateDate = QvdCreateTime('$(vSaveQVD)SunquestTAT.qvd');

Let vUpdateDate = Date('$(vQVDCreateDate)');

ODBC CONNECT TO CSQLULAB2010 (XUserId is FIYXbPNKZaUIVCVJFH, XPassword is BFLNRJFLTSYA);

Start of Incremental Load:

IncrementalSunquestTAT_Load1: // Naming file or routine

LOAD *;

SQL Select *

From SITE.U_GL_ACC_VIEW;

Store IncrementalSunquestTAT_Load1  into 'C:\Qlikview\Production\Data Echosystem\QVDs\IncrementalSunquestTAT_Load1.qvd';
// Storing initial load into local file

// Checking load against create date

// I cannot get this part to work!!

IncrementalSunquestTAT_Load1:

LOAD *;

SQL Select *

From SITE.U_GL_ACC_VIEW

Where ReceiveDateODBC >= $(vUpdateDate);

Any help will be immensely appreciated!

[Colin-Albert]

This looks like the SQL query rejecting the values in your variable as an invalid date.

What does the trace on yourdatevariable return?

[#3]

Null value

[Colin-Albert]

A null indicates that the script is not looking up the created date from the QVD file.

My guess is that there is a slash missing from this line

     Let vQVDCreateDate = QvdCreateTime('$(vSaveQVD)SunquestTAT.qvd');

Should it be

     Let vQVDCreateDate = QvdCreateTime('$(vSaveQVD)\SunquestTAT.qvd');

Have you tried using DEBUG mode to step through the load script line by line?

Click Debug in the script editor then click Step to advance line by line.

The centre of the screen shows the command that will be executed next and will expand any variables.

[#5]

Yes I’ve used the debug. I will give what you are suggesting a try.

Thanks!

[#6]

Albert, do you have an example of a full incremental load? I think that would be helpful for me. I am running into problems because of the way that dates are stored in Cache; also, I don’t have a primary key – so I am trying to create on in qlikview.

Concatenate

LOAD *

From 'C:\Qlikview\Production\Data Echosystem\QVDs\IncrementalSunquestTAT_Load1.qvd' (qvd)

where Not Exists (NumAccNumber,ReceiveDateODBC);

STORE IncrementalSunquestTAT_Load1 into 'C:\Qlikview\Production\Data Echosystem\QVDs\IncrementalSunquestTAT_Load1.qvd';

[Colin-Albert]

Try this example.

This is designed for data that is no longer being changed. The script will split the file into separate QVD's for each month. These can be loaded using a wildcard 

     Tran:

     load * from TRAN_*.qvd (qvd) ;

The file will need updating to add your database connections and set the correct file paths for your environment.

If your has old records that can still be modified then a different approach that checks for deletions and updates wityhin the existing data must be used.

Hope this helps.

Colin