Incremental Load Help advice

[ivandrago]

Hi,

I am currently have a scheduled task that is creating a qvd that is taking on average 45 minutes. Obviously this is taking a big hit on the server performance, making other shceduled tasks taking while to run.

The document that creates the qvd is reloading the same orders again, how do i check the original qvd created and see if that order already exist and then only load new ones from the database.

Has anyone got a understanding script or qvd with a example excel spreadsheet that would show a good example.

Thanks

[tanelry]

I suggest search QV help file for word "incremental"

And check the links in the box "more like this" here ->

(for example this)

[ivandrago]

Thanks for your help so far, I have the attached a example qvd document that generates the qvd file and I also attached a spreadsheet which the qvd document looks for.

On the spreadsheet it has a timestamp, if I was to add a new line with a new timestamp I want the qvd document to only insert new rows where the timestamp has not been added before? Does this make sense?

Thanks

[ivandrago]

Anyone

[#5]

Hi,

I have changed the script in your document and uploaded the new document. Check it.

HTH

Reg,

Shubhu

[ivandrago]

Hi Shubhu, Thanks for your help so far, I have ran the Document but I get the following error:

Cannot open file 'c:\Incre\Incr_QVD.qvd

group by 1'

maxdatetab:

load max(Timestamp) as mdt

From c:\Incre\Incr_QVD.qvd

group by 1

Please note the I have saved the qvd file to another location c:\Incre\

Thanks

[ivandrago]

Hi, not getting error now, I've changed the qvd file location, see the latest attached document and updated spreadsheet.

I expected though, that if the spreadsheet was showing the following at some point:

Type     Amount     Timestamp

AA       25              16/09/2011 09:15:00

And then I deleted this row from the spredsheet, to still appear in the qvd?

Thanks

[Anonymous]

Hello

I work with incremental load to day.

Comissão_de_Vendas:

LET data = today()- 68;

LOAD
fields

FROM
D:\table_name.qvd(qvd)
WHERE DATE < '$(data)';

CONCATENATE

LOAD
fields

FROM D:\table_name.qvd(qvd)
WHERE DATE > TRUNC(sysdate - 69);

Store Comissão_de_Vendas into 'D:\Desenv\Qvd\Comissão_de_Vendas.qvd';

[tanelry · Accepted solution]

IvanDrago, if you followed Shubhu's example then I believe you need to delete the first part of script where qvd was created.

I made some optimizations on this example for better load speed.

directory; // use current
MainTable:
LOAD Type,
     Amount,
     Timestamp
FROM Incr_QVD.qvd (qvd);
// load max timestamp, this method is faster than resident load
maxdatetab:
LOAD
MaxString(Timestamp) as mdt
;
LOAD fieldvalue('Timestamp',iterno()) as Timestamp
AUTOGENERATE 1
WHILE not IsNull(fieldvalue('Timestamp',iterno()))
;
let vMaxdate = FieldValue('mdt', 1);
DROP Table maxdatetab;
Concatenate (MainTable)
LOAD Type,
     Amount,
     Timestamp
from IncrementalTest.xlsx
(ooxml, embedded labels, table is Sheet1)
Where (Timestamp > '$(vMaxdate)');
STORE MainTable into Incr_QVD.qvd (qvd);

[ivandrago]

Hi Both,

I've added the script but it keeps failing not sure where the issue is as does not give a valid error, just says "Execution of script failed. Reload old data", I have attached the Document and spreadsheet.

Thanks