topic Re: Partial Reload Help in QlikView

Partial Reload Help

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have an invoicing report where I concatenate data from various Excel files and from my internal system through an ODBC link. The Excel files are very big as they have historical invoicing data.

What I want to do in order to make load times faster is to when I use Reload or Partial Reload I only update the part from the system, without reloading these files as I rarely modify them. Any idea on how to do that?

The system data is only the current month's information.

I tried using ADD and REPLACE to this section in my script, but as you can see below (blue bars), ADD just duplicate the data that I had before for this section and REPLACE erases all data (even from the Excel files) and leave only the system data.

ORIGINAL

WITH ADD

WITH REPLACE

Re: Partial Reload Help

Anonymous — Fri, 21 Jul 2017 20:59:04 GMT

You need to use an if statement with the IsPartialReload() function.

With that you can do more than

IF IsPartialReload()=True Then

CONCATENATE(YourOriginalTable)

Load *

From YourTable

That way you can do other commands besides just ADD and REPLACE.

You can do a concatenate to an existing table in the datamodel.

Re: Partial Reload Help

Anonymous — Mon, 24 Jul 2017 11:19:19 GMT

Hi Wallo,

I'm rather new to QlkView, where do I find the table name in order to to add after Concatenate?

Re: Partial Reload Help

Mon, 24 Jul 2017 11:58:26 GMT

You might find it easier to just separate your two extracts to produce a few QVD files, and then load these QVD files into your user interface application. Then you can run the relevant extracts as required, and the UI application will pick up the latest QVD data file. The load of the QVD files should be relatively fast, and it will make your logic easier to follow. The partial reloads do work, but it ultimately means you have to manage multiple workflows in the same load script code which can be challenging for new users trying to learn Qlikview.

It depends on a number of factors (e.g. volume of updated data to new data being loaded) as to whether using a partial reload will be faster or not (quite often loading from QVD is actually faster, as you need to filter the data to be excluded from the data set in memory when using a partial reload, or else you end up with the duplicate records you're seeing above). With partial reloads, you either run a Full Reload or a Partial Reload - so you need some other mechanism to identify which data sets it is you want to load when you run the partial reload (this can be achieved in many ways - e.g. config file, checking dates/timestamps in data vs source, etc). With the individual extract.qvw's, you can control which extracts occur simply by running the relevant task. The UI just picks up the latest data that has been extracted to QVD.

So you end up with (simple example):

ExtractA.qvw -> Extracts data to A.qvd

ExtractB.qvw -> Extracts data to B.qvd

UserInterface.qvw -> Loads data from A.qvd and B.qvd.

Re: Partial Reload Help

Anonymous — Mon, 24 Jul 2017 14:05:28 GMT

It would be the name of the table you need to update in your table viewer.

Graeme has a point though. Load your Excel files into 1 or more QVDs. You should then be able to do an incremental load on them to keep them updated. http://www.learnallbi.com/incremental-load-in-qlikview-part1/

Then in your application with your chart load the QVDs.

Re: Partial Reload Help

Anonymous — Mon, 24 Jul 2017 16:57:01 GMT

Graeme,

How do I save as QVD?

Re: Partial Reload Help

Tue, 25 Jul 2017 07:30:47 GMT

Take a look at the STORE command in the manual. The QVD's can then be loaded from just like a CSV/XLS file (e.g. load * from xyz.qvd (qvd);). There's plenty of information in the manuals, a few relevant excerpts below.

Store

A QVD or a CSV file can be created by a store statement in the script. The statement will create an explicitly named QVD or CSV file. The statement can only export fields from one logical table. The text values are exported to the CSV file in UTF-8 format. A delimiter can be specified, see Load. The store statement to a CSV file does not support BIFF export.

store[ *fieldlist from] table into filename [ format-spec ];

*fieldlist::= ( * | field ) { , field } ) is a list of the fields to be selected. Using * as field list indicates all fields.

field::= fieldname [as aliasname ]

fieldname is a text that is identical to a field name in the table. (Note that the field name must be enclosed by straight double Quotation Marks in Scripting or square brackets if it contains e.g. spaces.)

aliasname is an alternate name for the field to be used in the resulting QVD or CSV file.

table is a script labeled, already loaded table to be used as source for data.

filename is the name of the target file. The interpretation of file name is similar to names in load statements, i.e. the directory statements apply.

format-spec ::= ( ( txt | qvd ) )

The format specification consists of a the text txt for text files, or the text qvd for qvd files. If the format specification is omitted, qvd is assumed.

Examples:

Store mytable into xyz.qvd (qvd);

Store * from mytable into xyz.qvd;

Store Name, RegNo from mytable into xyz.qvd;

Store Name as a, RegNo as b from mytable into xyz.qvd;

store mytable into myfile.txt (txt);

store * from mytable into myfile.txt (txt);

Reading Data from QVD Files

A QVD file can be read into or accessed by QlikView by the following methods:

Loading a QVD file as an explicit data source. QVD files can be referenced by a load statement in the QlikView script just like any other type of text files (csv, fix, dif, biff etc). The File Wizard: Type handles QVD files by the same principles.

Examples:
load * from xyz.qvd (qvd);
load Name, RegNo from xyz.qvd (qvd);
load Name as a, RegNo as b from xyz.qvd (qvd);
Automatic loading of buffered QVD files. When using the Buffer prefix on load or select statements, no explicit statements for reading are necessary. QlikView will determine the extent to which it will use data from the QVD file as opposed to acquiring data via the original load or select statement.
Accessing QVD files via the script. A number of script functions (all beginning with qvd) can be used for retrieving various information on the data found in the XML header of a QVD file. These functions are presented under the File Functions in Script functions.