topic Re: Incremental Load script in QlikView

Incremental Load script

marsmike — Fri, 07 Mar 2014 18:59:10 GMT

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.

Re: Incremental Load script

Gysbert_Wassenaar — Fri, 07 Mar 2014 19:22:22 GMT

Perhaps this discussion helps: Incremental Load

Re: Incremental Load script

marsmike — Tue, 01 Apr 2014 18:28:41 GMT

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!

Re: Re: Incremental Load script

marsmike — Tue, 01 Apr 2014 18:32:56 GMT

Sorry, forgot to attach the script document...

Re: Incremental Load script

maxgro — Tue, 01 Apr 2014 18:41:21 GMT

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

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

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

Re: Incremental Load script

marsmike — Wed, 02 Apr 2014 10:32:33 GMT

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

Re: Incremental Load script

maxgro — Wed, 02 Apr 2014 12:32:31 GMT

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)#

;

Re: Incremental Load script

richard_pearce6 — Wed, 02 Apr 2014 12:54:03 GMT

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

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

Re: Incremental Load script

marsmike — Wed, 02 Apr 2014 14:34:04 GMT

It's an Oracle DB.

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

Re: Incremental Load script

jonathandienst — Wed, 02 Apr 2014 14:45:13 GMT

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.

Re: Incremental Load script

marsmike — Wed, 02 Apr 2014 15:36:40 GMT

Jonathan, thanks.

I have confirmed with the DB support team that the underlying field in the Oracle DB for "Last Refresh Date" is:

"M/D/YYYY hh:mm:ss TT"

where "TT" is "AM" or "PM".

When I print vFinishTime to a text box in QlikView, it has the same format.

I am familiar with the debugger and have been using it. That's how I know which line the error is on.

I made the two changes that you suggested, and get this error on the WHERE line: literal does not match format string".

Mike

Re: Incremental Load script

maxgro — Wed, 02 Apr 2014 15:38:55 GMT

in an Oracle database, starting from your value 4/1/2014 11:43:46 AM (i suppose DD/MM, if not change the bold)

and OrderDate a date column

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

trace $(vFinishTime);

SQL

select

from Orders

where OrderDate > to_date('$(vFinishTime)', 'DD/MM/YYYY HH24:MI:SS')

;

Re: Incremental Load script

marsmike — Wed, 02 Apr 2014 16:10:14 GMT

Well, the script did not error out!

Now, I need to do two things; 1) understand what your lines are doing, and 2) verify data.

Can you tell me what these 3 lines are doing?

1) let vFinishTime = .......

2) trace $(fFinishTime)

3) where... "to_date...."

Looking better!

Mike

Re: Incremental Load script

maxgro — Wed, 02 Apr 2014 16:53:12 GMT

Date# = interpretation (string to number)

if you want to see the number, in a textbox

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

Date = formatting (number to string)

in another textbox

very good doc about dates (if you're new to Qlik, read all HIC docs)

QlikView Date fields

trace is just to print (for debug purpose) the variable vFinishTime when you reload

you can comment it

where OrderDate > to_date('$(vFinishTime)', 'DD/MM/YYYY HH24:MI:SS')

this is Oracle side

to_date is Oracle function to convert from string to date

vFinishTime is the Qlik variable I formatted in 1)

The variable is in the format you see in the second textbox

Orcle needs 'DD/MM/YYYY HH24:MI:SS' to convert the string to oracle date

Re: Incremental Load script

Gysbert_Wassenaar — Wed, 02 Apr 2014 17:32:15 GMT

If you already have a string 4/1/2014 11:43:46 AM and you're going to use the oracle to_date function then you can simply put that string in the variable as it is: SET vFinishTime = 4/1/2014 11:43:46 AM;

Re: Incremental Load script

marsmike — Wed, 02 Apr 2014 18:51:09 GMT

We have been using a hard coded date for vFinishTime. However, it won't be that way in the real script. Since I only want to fetch records that have been updated since the last time that the query ran, at the end of the script, I set vFinishTime, like this:

let vFinishTime = date(date#(Now,(1), 'MM/DD/YYYY hh:mm:ss tt'), 'MM/DD/YYYY hh:mm:ss');

Then my WHERE clause looks the same as your suggestion:

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

However, I would like the format to be "M/D/YYYY hh:mm:ss TT", not in 24 hour format and not in 2 digit months and days. I have tried a number of different changes to the 2 lines above, but have not been able to do that.

Re: Incremental Load script

maxgro — Wed, 02 Apr 2014 20:08:35 GMT

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

trace $(vFinishTime);

SQL

select

from Orders

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

;

Re: Incremental Load script

Thu, 03 Apr 2014 08:02:52 GMT

Hi, Mike

you can change DateFormat according to your Oracel DB format.

and FinishTime is lastReloadTime?

if that's right, How about using ReloadTime.

ReloadTime() = 2014-04-03 PM 3:19:01

date(ReloadTime()) = 2014-04-03

Re: Incremental Load script

marsmike — Thu, 03 Apr 2014 10:31:36 GMT

Back at work this morning...

Massimo, I will try your changes to get the format I want. Question: What does Now(1)-1000 do?

Richard, I do not have access to the database itself. I did try once to use the Num function on vFinishTime. But I did not also format "Last Refresh Time" as Num('Last Refresh Time') so maybe if formatted both that way it would work. I know there is an "Interval" function in QV. I would PREFER to use the built-in functions in QV, rather than Oracle, but that preference is not mandatory. I just don't have an Oracle background so it's easier [for me] to use the built-in functions.

Would the "Interval" function work to compare dates formatted with the "Num" function?

Kim, I am unfamiliar with the function "ReloadTime" so I will have to look at that. I don't know what it does.

Re: Incremental Load script

maxgro — Thu, 03 Apr 2014 11:00:15 GMT

Now(1)-1000? 1000 days before now

I used it (-1000) to check the script works on my oracle database with your format

I have old data in my db, this is the reason I used -1000; remove -1000.

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

trace $(vFinishTime);

SQL

select

from Orders

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

;