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.

[Gysbert_Wassenaar]

Perhaps this discussion helps: Incremental Load

[marsmike]

I appreciate your help, but unfortunately nothing has worked so far.  Part of the problem is probably because [everyone here] assumes that I am a professional programmer or SQL developer.  Neither could be farther from the truth, although I really do like scripting in QlikView.

Again, I do not know why I cannot cut and paste directly into this window, so I am attaching my script.

I am trying to pull only those records from a database that have been updated since the last time the query was executed.

The field of the DB record that holds the updated time is "Last Refresh Date".  The last time the query was executed is being saved to a variable called "vFinishTime".

I set vFinishTime on the last line of the script.

The next time the query runs, I only want to fetch those records that have a "Last Refresh Date" that is newer than vFinishTime (meaning that the DB record was updated after the last query execution).

Every time the query runs, it terminates on the WHERE clause:  WHERE "Last Refresh Date" >= $(vFinishTime);

The script error box shows that line as:  WHERE "Last Refresh Date" >= 4/1/2014 11:43:46 AM

which seems okay but the SQL error is:  "SQL command not properly ended".

All help is appreciated!

[marsmike]

Sorry, forgot to attach the script document...

[maxgro]

which database? sometimes sql syntax and date format depends on database

try with   ' .......... '

WHERE "Last Refresh Date" >= '$(vFinishTime)';

[marsmike]

When I add the single quotes around, like you suggest, I get an error saying "not a valid month".

[maxgro]

is your database an access database?

this works for me in access

let vFinishTime='03/25/2014 12:59:00';

LOAD *;

SQL SELECT *

FROM Orders

where OrderDate >= #$(vFinishTime)#

;

[richard_pearce6]

Hi Mike,

The logic of your code makes sense and it does look like a formatting issue,

Do you have access to the database? Can you change or create a new field for Last Refresh Date and format it as a number.

So instead of '02/04/2014 13:51:18' you have '41731.577291667'

In the QlikView Script change

LET vFinishTime = Now(1);

to

LET vFinishTime = num(Now(1));

Working with dates can be a challenge, I always find numbers are much easier.

Hope it helps

Richard

qlikcentral | Understand / Create / Inform

[marsmike]

It's an Oracle DB.

The changes you suggested resulted in the error:  "invalid character" on the WHERE line.

[jonathandienst]

Mike

The important thing is that your variable vFinishTime must be in the same format that works in Oracle. You can test this using a query tool like Toad to execute the query and confirm the date format. (You could use Qlikview for this - create a model that opens the ODBC connection and executes the query). Try different date formats until you get it right.

Once you have the date format, use (for example, if the finish time is a timestamp in YYYY/MM/DD hh:mm:ss format):

     Let vFinishTime = TimeStamp(vFinishTime, 'YYYY/MM/DD hh:mm:ss');

(This assumes that vFinishTime is also a date or timestamp value). Confirm that vFinishTime is in the correct format and then use:

SQL SELECT *

FROM Orders

where OrderDate >= '$(vFinishTime)'

HTH

Jonathan

PS:If you open the debugger and step through until just before the command, you can see the actual query that QV sends to the database.