Qlikview Export Chart to QVD

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):

 

Qlikview Export Chart to QVD

 

They end up with a single row in the filter:

 

Qlikview Export Chart to QVD

 

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:

 

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:

Export format
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:

 

Qlikview Export Chart to QVD

 

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.

 

Qlikview Export Chart to QVD

 

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:

 

Qlikview Export Chart to QVD

 

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:

 

Variables:

 

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:

Partial Reload:  This command executes only Load and Select (SQL) statements preceded by a Replace or Add prefix. Other data tables remain unaffected by the command.

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).

 

Qlikview Export Chart to QVD

 

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.

Table Box:

 

Qlikview Export Chart to QVD

 

Filtered to players with Injury:

 

Qlikview Export Chart to QVD

 

Conclusion

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.

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

5 thoughts on “Qlikview Export Chart to QVD

  1. Thank you for your good articles. It is very helpfull for my start in ‘qlikview’.

    Bytheway,
    If you corrected ‘5 to 4’, so correct the final ‘biff’ to ‘qvd’ too. 🙂
    …FROM [C:\QVD\PlayerStats.qvd] (biff, embedded labels, table is Sheet1$);
    should be:
    …FROM [C:\QVD\PlayerStats.qvd] (qvd, embedded labels, table is Sheet1$);

    BR

  2. Thanks Admin

    And thanks for a very interesting article that gave me pointers for ways to export charts to QVD
    That method can be very useful although I am sure some people would rather build the target table in load script then use Store command.

    I can see reasons why the charts would be a nice alternative to this more standard approach.

  3. Actually

    set obj = ActiveDocument.GetSheetObject(“CH06”) obj.ExportEx QVDFile, 5
    should in fact read
    set obj = ActiveDocument.GetSheetObject(“CH06”) obj.ExportEx QVDFile, 4

    The parameter 5 is for BIFF format while 4 is QVD
    see https://community.qlik.com/thread/4586 which lists all possible values for the parameter

    Regards

Leave a Comment