Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
marsmike
Creator
Creator

Incremental Load script

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.


47 Replies
nizamsha
Specialist II
Specialist II

marsmike
Creator
Creator
Author

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

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:

MMNumeric month (e.g., 07)
MONAbbreviated month name (e.g., JUL)
MONTHFull month name (e.g., JULY)
DDDay of month (e.g., 24)
DYAbbreviated name of day (e.g., FRI)
YYYY4-digit year (e.g., 1998)
YYLast 2 digits of the year (e.g., 98)
RRLike 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
HHHour of day (1-12)
HH24Hour of day (0-23)
MIMinute (0-59)
SSSecond (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
Former Employee
Former Employee

Hi Mike,

See if either of these will help you.

Bill

Bill - Principal Technical Support Engineer at Qlik
To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.
marsmike
Creator
Creator
Author

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
Creator
Creator
Author

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
Former Employee
Former Employee

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

Bill - Principal Technical Support Engineer at Qlik
To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.
marsmike
Creator
Creator
Author

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
Creator
Creator
Author

....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
Creator
Creator
Author

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