topic Re: Creating Incremental Load from Cache Database in QlikView

Creating Incremental Load from Cache Database

Tue, 07 Jan 2014 19:45:07 GMT

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!

Re: Creating Incremental Load from Cache Database

Tue, 07 Jan 2014 20:00:14 GMT

Meani,

Apologies, but i accidentally rejected your request into the HC users group. Can you submit again and i can accept it this time?

matt

Re: Creating Incremental Load from Cache Database

Colin-Albert — Tue, 07 Jan 2014 20:11:00 GMT

I would include a trace command to check that $(vUpdateDate) is formatted correctly

TRACE UPDATEDATE >$(vUpdateDate)< ;

You may need to use date() and /or date#() to convert to the correct format to match your database date fields.

Finally you may need to add single quotes around your where clause once the date format is correct.

From SITE.U_GL_ACC_VIEW

Where ReceiveDateODBC >= '$(vUpdateDate)' ;

Re: Creating Incremental Load from Cache Database

Tue, 07 Jan 2014 20:13:32 GMT

Thank you! I will give this a go.

Re: Creating Incremental Load from Cache Database

Tue, 07 Jan 2014 20:19:32 GMT

I apologize for my lack of knowledge. I am new to QlikView. I have no idea where to put the trace command. Would this go before the where clause or after?

Re: Creating Incremental Load from Cache Database

Colin-Albert — Tue, 07 Jan 2014 20:23:44 GMT

I would put it on the line after

Let vUpdateDate= ....

Re: Creating Incremental Load from Cache Database

Tue, 07 Jan 2014 20:57:56 GMT

Thanks!

Re: Creating Incremental Load from Cache Database

Tue, 07 Jan 2014 21:09:56 GMT

Albert, this is the error that I am getting.

Re: Creating Incremental Load from Cache Database

Colin-Albert — Tue, 07 Jan 2014 21:16:59 GMT

Hi Meani,

Oops - no error message

Re: Creating Incremental Load from Cache Database

Tue, 07 Jan 2014 21:24:20 GMT

“sqlcode:<-146> Unable to convert Date input to a valid Logical Date value”

Re: Creating Incremental Load from Cache Database

Colin-Albert — Tue, 07 Jan 2014 21:32:56 GMT

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

What does the trace on yourdatevariable return?

Re: Creating Incremental Load from Cache Database

Tue, 07 Jan 2014 21:34:32 GMT

Null value

Re: Creating Incremental Load from Cache Database

Colin-Albert — Tue, 07 Jan 2014 21:47:30 GMT

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.

Re: Creating Incremental Load from Cache Database

Tue, 07 Jan 2014 21:50:08 GMT

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

Thanks!

Re: Creating Incremental Load from Cache Database

Tue, 07 Jan 2014 22:05:08 GMT

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';

Re: Re: Creating Incremental Load from Cache Database

Colin-Albert — Tue, 07 Jan 2014 22:50:51 GMT

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