topic Re: Doing a partial reload in QlikView

Doing a partial reload

Sun, 15 Sep 2013 23:27:30 GMT

I am working with a number of excel files. Some of them are quite large. The qlikview reload, hence, takes quite a few minutes to reload.

If i make a change to one of the base excel files, i have to do the complete reload again and wait which makes the whole process inefficient. Is it possible to only reload the specific file which was changed? And not having to reload the whole set of files which takes ages?

Would be great if you could let me know.

Re: Doing a partial reload

Mon, 16 Sep 2013 12:10:54 GMT

You can add a conditional statement to the reload using filetime()

In that case you'll only reload files which are modified today (or after a certain time)

filetime()

Returns a timestamp for the date and time of the last modification of the file filename. If no filename is specified, the function will refer to the currently read table file.

Examples:

filetime( 'xyz.xls' )

Will return the timestamp of the last modification of the file xyz.xls.

Load *, filetime() as X from abc.txt ;

Will return the date and time of the last modification of the file (abc.txt) as a timestamp in field X in each record read.

Re: Doing a partial reload

Mon, 16 Sep 2013 12:17:31 GMT

Hi - where do I add this? in the Edit script section?

For example so in Edit Script, I type

=filetime(To Be data 16.9.xlsx) and then hit Reload? That will reload everything and come up with an error message.

Re: Doing a partial reload

jonathandienst — Mon, 16 Sep 2013 12:20:25 GMT

Loading from Excel files can be slow. A quick solution is to use Qlikview's buffer command. Just prefix your load like this:

Buffer (Stale After 24 hours)

LOAD .....

Buffer

LOAD ....

Adjust the time to nn hours or nn days at your discretion. If you do not modify the load statement, the buffer will read from a local QVD which is much faster than reading from Excel.

Hope that helps

Jonathan

Re: Doing a partial reload

Mon, 16 Sep 2013 12:29:15 GMT

First of all, Qlikview drops your data upon a reload, so if you don't want to reload everything that means you'll loose data.

Solution 1 is to change your script so you load your tables first into QVD's.

------------------------------

Tablename:

load statement

FROM

[\YourFILE.xls]

(biff, embedded labels, table is [sheet1$]);

Store Tablename into $(vQVDFolder)\Tablename.QVD (QVD);

-------------------------------

If you load from the QVD it'll be much faster to reload.

Solution 2:

Create subs, so you can easily switch off and on certain parts of your load script (for instance to just load that one excel file into qvd)

-----------------------------

Sub YOURSUB;

your script

End sub

Sub YOURSUB2;

your script

End sub

Call YOURSUB;

-------------------------------

This will only execute the code under YOURSUB.

Finally you can use 'first n' to specify a number of rows you want to load, this way you'll have all tables only with limit data, which speeds up load times.

Re: Doing a partial reload

Mon, 16 Sep 2013 15:34:42 GMT

You can use a variable and an If ... Then... clause

If vFlagForReload=1 Then

replace Load

...

From... SomeExcel_1 ....

End If

Set value for vFlagForReload with a button and with the same button make reload

well... with this construction you can e_x_c_l_u_d_e from reload

if vFlagForReload<>1 then your script escape reloading from file SomeExcel_1

Re: Doing a partial reload

weezerian — Tue, 17 Sep 2013 06:30:24 GMT

Just use "REPLACE" in front of your script..For example :

Example:

REPLACE LOAD

aa,

bb,

FROM

[..\excel.xls]

(biff, embedded labels, table is Sheet1$);

you have to reload with "CTRL+SHIFT+R" or you can go "file-->Partial Reload"

Re: Doing a partial reload

Tue, 17 Sep 2013 09:34:28 GMT

Perfect this worked! Thanks. The other answers are too confusing for a beginner like me to work out of.