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]

My woes continue...

I watched the QV video on how to do an incremental load.  He made it look real easy.  But there were a couple things that he could have done better.

But here's my issue, which he did NOT have in the video:  in my source database, there are fields with spaces in the names; I have already mentioned the important one - "Last Refresh Date".

When I recreated a script to go along with the video, I cannot get it to work.  But if I change "Last Refresh Date" to "Last_Refresh_Date", it works fine.

I know that spaces are an issue.  I know that double quotes and the brackets can be used to enclose names with spaces.  But, I have not been able to come up with the right combination of double quotes, single quotes and brackets to make it work.

Here are the two relevant lines:

Order by "Last Refresh Date" asc;

Let vLastRefreshDate = Peek('[Last Refresh Date]', -1, 'Sort_Last_Refresh_Dates');

This results in a <NULL> value for vLastRefreshDate.

[Bill_Britt]

Hi Mike,

Have you tried to "Date" the Peek function?  Also, is the Last Refresh date in the table Sort_Last_Refresh_Dates?

Bill

[marsmike]

Bill, I just tried renaming the field in my initial load:

Load "Last Refresh Date" as Last_Refresh_Date

This seems to be working.  I am now cotinuing through the video to the next step.

[Bill_Britt]

Hi Mike,

The peek function is listed as

peek(fieldname [ , row [ , tablename ] ] )

Bill

[marsmike]

The guy doing the video said the "-1" parameter would pull the last row...in this case, because the query is ordered by the date column in an ascending manner, it gets the last date.  It appears to be working.

There are 4 steps to the process, as outlined in the video.  I am on step 3.

[Bill_Britt]

Hi Mike,

I am referencing the 'Sort_Last_Refresh_Dates' you have listed as the last item.

Let vLastRefreshDate = Peek('[Last Refresh Date]', -1, 'Sort_Last_Refresh_Dates');

Is this a table? peek(fieldname [ , row [ , tablename ] ] )

[marsmike]

Yes, it's a table:

Sort_Last_Refresh_Dates:

LOAD *

Resident Incidents

Order by Last_Refresh_Date asc;

Let vLastRefreshDate = ......

[maxgro]

this is the script I used to do an incremental load test on an oracle database; for my environment it works

I put in bold the things that depend on environment (date, time, etc......, oracle and qlik)

also in the attachment

oracle table    

create table z_Incidents (id integer, last_refresh_date date default sysdate)

qlikview script

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

SET DayNames='lun;mar;mer;gio;ven;sab;dom';

// add your conn

OLEDB CONNECT32 TO .................;

DIRECTORY;

set vfilename='Incidents.qvd';

let vfileexists=if(FileSize('$(filename)')>0,-1,0);

trace vfileexists=$(vfileexists);

if $(vfileexists) then

  trace ***** file exists;

  // load from qvd

  Incidents:

  load * from '$(vfilename)' (qvd);

  // max refresh date, first from qvd, if missing

  // use a constant in the format of oracle to_date 'DD/MM/YYYY HH24:MI:SS'

  LET vLastRefreshDate = alt(peek('last_refresh_date', -1), '01/01/1900 00:00:00');

  TRACE ***** vLastRefreshDate=$(vLastRefreshDate);

  // add from database

  Concatenate (Incidents)

  LOAD

  ID as id ,

     LAST_REFRESH_DATE as last_refresh_date;

  SQL SELECT

  ID,

  LAST_REFRESH_DATE

  FROM DWH_STAGING.Z_INCIDENTS

  WHERE LAST_REFRESH_DATE > to_date('$(vLastRefreshDate)', 'DD/MM/YYYY HH24:MI:SS');

ELSE

  trace ***** file not exists;

  // from database, no filter

  Incidents:

  LOAD

  ID as id ,

     LAST_REFRESH_DATE as last_refresh_date;

  SQL SELECT *

  FROM DWH_STAGING.Z_INCIDENTS;

ENDIF;

// store in qvd for next reload

STORE Incidents into '$(vfilename)' (qvd);

[marsmike]

Well, it looks like my woes continue...

While I was able to recreate the process that was on the QlikView video, there certainly were some issues.  While this line works in the video example:

"Where "Last Refresh Date" >= $(vLastRefreshDate);

The video pulls data from local Excel files.  This line does not work when connecting to the Oracle database.  I am trying everything and have run out of ideas.  I am now going back over all previous suggestions to see if there is something else I have not yet tried.

[marsmike]

PS:  I know that a "PS" is supposed to go at the end, but forgive me, I am doing it first before I forget:  why can't I past into this forum?  Is there something in the setup that prevents it?  It would be so much easier if I could...

I have not yet been able to make this work with my Oracle database.  It works if I get the data from a local Excel file but that's not the case in production.  It worked well in the QlikView video but not well in the "real" world.

Nothing has worked so  far.  I have modified the script, based on the QV video which, I'm sorry to say, leaves somethin to be desired....here's the script:

// Main

LET vQVDCreateDate = QvdCreateTime('$(QVDpath)incidents.qvd(qvd);

LET vRefreshDate = Date('$QVDCreateDate)', 'M/D/YYYY hh:mm:ss TT');

// Initial Load

SQL SELECT

               "Incident Number",

               "Last Refresh Date"

FROM "DB1"."INCIDENT_REPORT";

Store Incidents into $(vQVDpath)Incidents.qvd(qvd);

// Incremental Load

SQL SELECT

               "Incident Number",

               "Last Refresh Date"

FROM "DB1"."INCIDENT_REPORT

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

Everything works up until the Incremental Load.  The error is:  "date format picture ends before converting entire input string"

I appreciate everyone's help and apologize for taking up your time.

Sincerely,

Mike