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.

[richard_pearce6]

Hi Mike,

I had the same problem and error message yesterday trying to incremental load from an oracle database. I resolved it using the script below

vMaxMatchDate = Timestamp(peek('MAX_MATCHDATE',0,'MAX_MATCHDATE'),'DD-MMM-YYYY h:mm:ss');

Load

*;

Select *
Where M.MatchDate > TO_DATE ('$(vMaxMatchDate)', 'DD-MON-YYYY HH24:MI:SS');

The format of vMaxMatchDate when I set the variable is the same format in the where clause. Looking at yours it is different.

Also, I noticed the formatting of the TO_DATE function is different to Qlik timestamp for Month (i.e.. MMM is MON).

I would experiment with the above. I read your oracle uses TT, try it with and without but keeping them both consistent.

Good Luck

Richard

qlikcentral.com

[marsmike]

Richard, thanks.  Can you enlighten me on a couple things?

First, the second parameter in the PEEK is "0".  My understanding is that the zero will look at the FIRST record.  That implies that the data is sorted, which is what the QlikView video was specifically eliminating.  Now, I don't really care which way I do it (the first QV video used sorting).  But if your PEEK is looking at the first record, then I need to sort my "Last Refresh Date" field in an ascending order and then use the parameter "-1" to look at the last (most recent) updated record.

Do you agree?

I have been saving the different versions of the app, so if you agree I will go back to the version with the ORDER BY clause.  I have tried many, many (did I say many?!) combinations, and at times tried to make sure that the formatting of the dates is the same.  I will make the changes and report back, hopefully by tomorrow.

Thanks to all.

Mike

[richard_pearce6]

Hi Mike,

Sorry I should have edited my code a little for more context.

This is the table code I am peeking:

MAX_MATCHDATE:

Load

      Max(MAX_MATCHDATE) as MAX_MATCHDATE

From [previously_loaded_data.qvd] (qvd);

This gives me one record, which I peek to get the vMaxMatchDate (which I then use to query the oracle database.)

I hope this helps

Richard

QlikCentral.com

PS - You can't order a QVD load, you have to load it first and then again as resident to order it..... http://community.qlik.com/message/211554#211554

Also, Small correction to my previous reply. MM works in the oracle date format function TO_DATE as explained by Massimo Grossi, MON is used when you want months to show at Jan, Feb, Mar,,,,, either way it needs to be consistent between the variable and the TO_DATE function.

[marsmike]

I did the Resident before.

So in your code above, MAX_MATCHDATE is both the table name and a field name, correct?

[richard_pearce6]

Yes that's correct Mike, its only a temp table I drop after peeking.

Did you try making both the variable and where clause the same format timestamp?

[marsmike]

I have tried formatting both the variable and the WHERE clause to be the same.  I have not yet tried the TIMESTAMP function.

[richard_pearce6]

Morning Mike,

Maybe worth trying timestamp rather than date for the variable keeping the DD/MM/YYYY hh:mm:ss the same for both. That resolved the "date format picture ends before converting entire input string" for me when I had it.

Richard

QlikCentral.com

[ecolomer]

You can see ... Incremental Loads are dead, long live the CSV