Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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.
Just use "REPLACE" in front of your script..For example :
Example:
REPLACE LOAD
aa,
bb,
cc
FROM
[..\excel.xls]
(biff, embedded labels, table is Sheet1$);
you have to reload with "CTRL+SHIFT+R" or you can go "file-->Partial Reload"
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.
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.
Hi
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 .....
or
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
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.
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
Just use "REPLACE" in front of your script..For example :
Example:
REPLACE LOAD
aa,
bb,
cc
FROM
[..\excel.xls]
(biff, embedded labels, table is Sheet1$);
you have to reload with "CTRL+SHIFT+R" or you can go "file-->Partial Reload"
Perfect this worked! Thanks. The other answers are too confusing for a beginner like me to work out of.