A QVD (QlikView Data) file is a file containing a table of data exported from QlikView. These data files maintain the compressed/optimized traits of QVW files, prove to be efficient, and can be reloaded 10-100 faster than normal data sources.
Recently, I was building an expression heavy straight table chart. Unfortunately, a user was unable to filter the content because most columns were built on expressions.
For instance, if the user tried to filter the following chart on column “Had Injury” (which is an expression):
They end up with a single row in the filter:
It would be better to have a List Box object for “Had Injury” where the user could select “Yes” or “No”, and have the contents of the chart filter accordingly.
I could simply create many of these columns in the LOAD SCRIPT and call it a day. However, if I wanted to introduce interactive chart features (letting users introduce variables into chart expressions), I would lose some of the dynamic functionality. I decided to export the chart to QVD, then re-importing the QVD into a data island. I will explain how to set this up below, using a simplified straight table for example purposes.
Implementing a Qlikview Export Chart to QVD
Once you have built your straight table chart, select “Tools – Edit Module” from the menu bar, then create the following macro:
QVDFile = "C:\QVD\PlayerStats.qvd"
set obj = ActiveDocument.GetSheetObject("CH06")
obj.ExportEx QVDFile, 4
Make sure you update the following two lines in the above script to be reflective of your own environment (text highlighted in Red):
- QVDFile = “C:\QVD\PlayerStats.qvd“
- set obj = ActiveDocument.GetSheetObject(“CH06“)
Important Note: Per comment below from Arnaud Blanche, it is important you set the proper Export Type in the following statement above:
line “obj.ExportEx QVDFile, 4”, the key is using Export Type = 4 (QVD)
Arnaud provides a link to the Qlik Community for additional detail:
0 = HTML
1 = Text delimited
2 = bitmap image
3 = XML
4 = QVD
5 = BIFF (Excel)
Next, setup a button object, and assign the following Actions:
I use a variable “vPartial” to indicate that the user is executing a Partial Reload (assigned value to “Yes” in the Set Variable action above). This comes into play in my LOAD SCRIPT, which is shown further down below: Select the “Run Macro” Action above and assign Macro Name “ExpQVD”, which is the macro you created above.
My QVW has multiple sheets. When you run a Partial Reload, the documents move to the first sheet in the hierarchy by default. Instead, I want the user to remain on the active sheet. Therefore, add an action to bring them back, using the Activate Sheet:
Make sure you update the Sheet ID with the proper sheet you want the action to active for the user. Now you are ready to add the LOAD SCRIPT to bring in the contents of your QVD file:
REPLACE LOAD PlayerName, Pts, Seasons, Games, [Rush Att], [Rush Yds], [100+ Rsh Yds], Recept, [Recpt Yds], [100+ Rec Yds], [Multi Team], [Had Injury]
FROM [C:\QVD\PlayerStats.qvd] (qvd, embedded labels, table is Sheet1$);
let vColHighlight = rgb(210, 255, 210);
let vColNoHighlight = rgb(255, 255, 255);
let vViewMode = ‘Select in Field’;
if vPartial = ‘Yes’ then
let vViewMode = ‘Play’;
let vPartial = ‘No’;
Note the Location of the QVD file in the first part of the script above. Also, make sure you add the “REPLACE” command to the LOAD statement, to designate a Partial Reload.
As stated in Qlikview Help:
I have conditional show expressions setup in each sheet of my QVW, so only one sheet is visible to a user at a given time. I also have an informational sheet that is visible at all times. This is why I need to set a few variables during LOAD. Don’t bother with this in your own implementation.
Once you click the “Refresh” button setup above, the Straight Table Chart is exported to a QVD file, then re-imported back into the QVW as a stand-alone table “PlayerStats” (sometimes called a data island).
The same straight table chart can now be setup as a Table Box object, and the user is now able to filter (using List Boxes) on fields contained within the table. Specifically, the user can now select “Had Injury” in the List Box and view only those players that actually had an injury.
Filtered to players with Injury:
As demonstrated above, having the ability to export the contents of a chart to a QVD file can be useful. In my case, I had a very expression heavy chart (one dimension with 10+ expressions). Many of the expressions resulted in content which users then wanted to filter on, to further refine the analysis. Subsequently, additional objects were setup and the user could successfully filter using results of the original expressions.
I like to use interactive variables for Straight Table Charts in some cases. Users can input variable values used in the chart expressions (Stress Testing, Threshold variables, Expression Limits, etc.). Using the technique described above, the resulting charts can be exported to QVD files, then re-imported and used in more detailed analysis.
I hope you found this post useful and encourage any comments or feedback.