Incremental Load script

[marsmike]

I am trying to write my first incremental load script in QV v11.  I am using the script that is in the reference manual.  This is what the script says:  well, I was not able to paste it in here....I have attached it as a file.

I am loading from a QVD file and the DB itself, concatenating and storing back into the QVD....so the QVD should grow by the amount of the new records found in the DB.

My question is about the line that reads:

WHERE ModificationTime >= #$(LastExecTime)

I understand what it's doing but do not understand how it is set up.

Looking for help, thanks.

[nizamsha]

This link will be helpful for u

Incremental Data Load in Qlikview « Learn Qlikview

[marsmike]

Sweet baby James!  You guys are great.  I appreciate all the different answers!

Massimo, your last suggestion worked great.  Dare I ask more questions?

Here is the script now:

LET vStartTime = Now(1);

Incidents:

SQL SELECT

     "Incident Number",

     "Last Refresh Date"

FROM "DB1"."INCIDENT_REPORT"

WHERE "Last Refresh Date" > to_date('$(vFinishTime)', 'MM/DD/YYYY HH:MI:SS');

// question:  why is the format "HH:MI:SS" instead of "HH:MM:SS"?  ( MI vs MM )?

// Here are the 3 lines that do the concatenation, load and store into the QVD file:

Concatenate LOAD * FROM $(vQVDPath)Incidents.qvd(qvd)

WHERE NOT Exists([Incident Number]);

STORE Incidents INTO $(vQVDPath)Incidents.qvd(qvd;

// question:  what does the (qvd) after "Incidents.qvd" do?

// last line

LET vFinishTime = date(Now(1), 'M/D/YYYY hh:mm:ss TT');

[maxgro]

question1: why is the format....

after SQL you're querying Oracle (Qlik send the statement to Oracle)

so the syntax after SQL is Oracle  syntax (different from Qlik)

an extract of more popular Oracle format options:

MM	Numeric month (e.g., 07)
MON	Abbreviated month name (e.g., JUL)
MONTH	Full month name (e.g., JULY)
DD	Day of month (e.g., 24)
DY	Abbreviated name of day (e.g., FRI)
YYYY	4-digit year (e.g., 1998)
YY	Last 2 digits of the year (e.g., 98)
RR	Like YY, but the two digits are ``rounded'' to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906
AM (or PM)	Meridian indicator
HH	Hour of day (1-12)
HH24	Hour of day (0-23)
MI	Minute (0-59)
SS	Second (0-59)

question 2: what does........

from online help

A QVD or a CSV file can be created by a store statement in the script. The statement will create an explicitly named QVD or CSV file. The statement can only export fields from one logical table. The text values are exported to the CSV file in UTF-8 format. A delimiter can be specified, see Load. The store statement to a CSV file does not support BIFF export.

store[ *fieldlist from] table into filename [ format-spec ];

*fieldlist::= ( * | field ) { , field } ) is a list of the fields to be selected. Using * as field list indicates all fields.

field::= fieldname [as aliasname ]

fieldname is a text that is identical to a field name in the table. (Note that the field name must be enclosed by straight double Quotation Marks in Scripting or square brackets if it contains e.g. spaces.)

aliasname is an alternate name for the field to be used in the resulting QVD or CSV file.

table is a script labeled, already loaded table to be used as source for data.

filename is the name of the target file. The interpretation of file name is similar to names in load statements, i.e. the directory statements apply.

format-spec ::= ( ( txt | qvd ) )

The format specification consists of a the text txt for text files, or the text qvd for qvd files. If the format specification is omitted, qvd is assumed.

Examples:

Store mytable into xyz.qvd (qvd);

Store * from mytable into xyz.qvd;

Store Name, RegNo from mytable into xyz.qvd;

Store Name as a, RegNo as b from mytable into xyz.qvd;

store mytable into myfile.txt (txt);

store * from mytable into myfile.txt (txt);

[Bill_Britt]

Hi Mike,

See if either of these will help you.

Bill

[marsmike]

That was good info, Bill, thanks.

Unfortunately, I may have spoken too soon on the success of the script.  It did run successfully at least once before I updated the thread here.  But now I am getting another error...rather long but will attempt to type it in accurately here.....

"SQL##f - SqlState: S1000, ErrorCode: 1830, ErrorMsg: [Oracle][ODBC][Ora]ORA-01830: date format picture ends before converting entire input string"

After getting this error, the only way I have found to get the script to run is to put this LET statement before the SQL LOAD statement:

let vFinishTime = date(date#('4/1/2014 11:43:46 AM', 'MM/DD/YYYY hh:mm:ss tt'), 'MM/DD/YYYY hh:mm:ss");

After the script runs, I can comment out this line and uncomment the same line at the end of the script and it runs successfully.

But, of course, I don't want to hard code vFinishTime.  I don't know why it ran once after making the chanes that Massimo suggested.

Is there documentation somewhere that explains all the SQL error messages?

Mike

[marsmike]

So I think I know why it ran once okay this morning after applying Massimo's changes....because the first time it ran, vFinishTime was still set to the hard coded date.  As soon as vFinishTime is set to Now(1)...even though the date & time is correct, the next time the script runs, it fails with the error above.

[Bill_Britt]

Hi Mike,

Why can't you do a Peek Function on the Incident Number and get the last number loaded and then do a load where incident number is greater than the peek variable?

Bill

[marsmike]

Hey Bill.

The records that I am trying to fetch are not based on the Incident Number itself, but on the "Last Refresh Date" of the record.  Those records might in numerical order by incident number, but older incident numbers can have a newer "Last Refresh Date".

Mike

[marsmike]

....and the QV reference manual actually shows methods of doing incremental loads, and that's what I'm basing it on.  In each case, dates are compared to the date of the last load.

[marsmike]

Nizam HM, thanks!  He made it look really easy!  I will redo my script this evening.  Hope to update here tomorrow.