topic Re: Incremental Load Help advice in QlikView

Incremental Load Help advice

ivandrago — Thu, 15 Sep 2011 14:57:21 GMT

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

Incremental Load Help advice

tanelry — Thu, 15 Sep 2011 16:22:59 GMT

I suggest search QV help file for word "incremental"

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

(for example this)

Re: Incremental Load Help advice

ivandrago — Thu, 15 Sep 2011 22:20:01 GMT

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

Re: Incremental Load Help advice

ivandrago — Fri, 16 Sep 2011 08:41:23 GMT

Anyone

Re: Incremental Load Help advice

Fri, 16 Sep 2011 09:29:08 GMT

Hi,

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

HTH

Reg,

Shubhu

Re: Incremental Load Help advice

ivandrago — Fri, 16 Sep 2011 10:05:05 GMT

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

Re: Incremental Load Help advice

ivandrago — Fri, 16 Sep 2011 10:49:47 GMT

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

Incremental Load Help advice

Anonymous — Fri, 16 Sep 2011 12:53:45 GMT

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

Re: Incremental Load Help advice

tanelry — Fri, 16 Sep 2011 14:19:46 GMT

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

Re: Incremental Load Help advice

ivandrago — Fri, 16 Sep 2011 15:42:49 GMT

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

Re: Incremental Load Help advice

tanelry — Fri, 16 Sep 2011 17:10:18 GMT

It's hard to tell. Does qvd file exist? Check the "generate logfile" in document properties before reload, then you can see where it fails.

Btw, you missed the directory path in the STORE line.

Re: Incremental Load Help advice

ivandrago — Mon, 19 Sep 2011 09:59:08 GMT

Hi Tanei,

Where are the log files kept as I have done the following:

Uncommented the directory path (Whatever that is meant to do?)

Reloaded the script but still get "Execution of script failed. Reload old data"

Gone to Settings > Document Properties > Generate Logfile

Thanks

Re: Incremental Load Help advice

tanelry — Mon, 19 Sep 2011 10:24:20 GMT

QV help file wants to be your friend (it is mine almost daily).

Anyway, .log file will be created next to .qvw.

And "Directory ;" lets you specify read/store location of files just once so you only need filename in the following load statements, instead of full path each time. No need to use it though, if confusing.

Incremental Load Help advice

ivandrago — Mon, 19 Sep 2011 10:41:46 GMT

Hi Tanei,

This works now, looks like it was a locking issue with the qvd, so I deleted the qvd and reloaded this again and working fine.

Thanks for your help!

Re: Incremental Load Help advice

Fri, 31 Jan 2014 09:08:45 GMT

Check the properties of the folder where the qvd files are stored and change the attributes to full access. (Mind you, one needs to be logged in as an administrator to make changes from Read Only to Full access).