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.

[marsmike]

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

[maxgro]

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

;

[marsmike]

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

[maxgro]

1)

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

2)

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

you can comment it

3)

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

[Gysbert_Wassenaar]

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;

[marsmike]

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.

[maxgro]

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

;

[#9]

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

or

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

[marsmike]

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.

[maxgro]

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

;