Optimized QVD Loads: caveats for using the Exists() function




As far as I know the Exists() function, in its simplest form ( Exists([Field]) ), is the only WHERE clause you can include when loading from a QVD that will maintain an optimized load.

A very convenient technique you can use to filter data when loading from a QVD, while maintaining an optimized load, is to pre-load the field on which you want to filter with the desired value(s) and then include the Exists() function in the LOAD statement from the QVD.

There are a couple of caveats to note in order to maintain the optimized load using the Exists() function:  1) the field referenced in the Exists() function must be included in the LOAD statement, and 2) the field must not be aliased.

The follow LOAD statements give examples of incorrect ways and the correct way to filter data in a QVD LOAD statement using Exists().

// Pre-load the filter value(s)
RIFilter:
LOAD 1 as [Record Include Flag] AutoGenerate(1)
RIFilter << AUTOGENERATE(1) 1 lines fetched
// Filter field not included in LOAD field list
FactTable:
LOAD  
[Effective Date] as EffDt
FROM  
FactTable.qvd (qvd)
WHERE   
Exists([Record Include Flag])
FactTable << FactTable 6,514,686 lines fetched <- Not optimized
 
 // Filter field included in LOAD field list and aliased
FactTable:
LOAD   [Effective Date] as EffDt,  
[Record Include Flag] as RIFlg
FROM  
FactTable.qvd (qvd)
WHERE  
Exists([Record Include Flag])
FactTable << FactTable 6,514,686 lines fetched  <- Not optimized
// Filter field included in LOAD field list and NOT aliased
FactTable:
LOAD
[Effective Date] as EffDt,
[Record Include Flag]
FROM
FactTable.qvd (qvd)
WHERE
Exists([Record Include Flag])
FactTable << FactTable (qvd optimized) 6,514,686 lines fetched <- Optimized


NOTE:  Tested on QlikView 11.2 SR3

TAGS: LOAD, QlikView, QVD, Data Model, Exists, Load Script, Optimized

Related News

IS YOUR COMPANY UPGRADING TO QLIKVIEW 12?

Axis Group was approached by a client asking for help...

Read More

Part 1 of this series describes what Qlik Sense does better than QlikView, from the perspective...

Read More