Storing multiple tables into a QVD in QlikView

Dexter picture Dexter · Aug 27, 2014 · Viewed 12.5k times · Source

Is it possible to store more than one table's data into a single QVD document?

If so, how can I both store and load multiple tables into/from this QVD?

Answer

i_saw_drones picture i_saw_drones · Aug 27, 2014

Storing tables

You cannot store more than one distinct table into a QVD file. The reason for this, is that the QVD format only stores the memory "image" of a single table and as such does not include metadata for any links to other tables.

Therefore, if you wish to store multiple tables in a QVD file you must either:

  • Store them as separate QVDs
  • Combine them into a single table (e.g. via JOINs, applymap etc.) and then store the table into a QVD file.

For example, say you have the following schema:

Example Table layout with two tables

Orders:
LOAD * INLINE [
    Product, Country, Value
    A, USA, 100
    B, UK, 200
    C, FR, 300
];

Products:
LOAD * INLINE [
    Product, Weight, Colour
    A, 10, Red
    B, 50, Yellow
    C, 70, Green
];

You cannot store this model into a single QVD file. Looking back to our two options:

Store as separate QVDs

You can change the script slightly to:

Orders:
LOAD * INLINE [
    Product, Country, Value
    A, USA, 100
    B, UK, 200
    C, FR, 300
];

Products:
LOAD * INLINE [
    Product, Weight, Colour
    A, 10, Red
    B, 50, Yellow
    C, 70, Green
];

STORE Orders INTO Orders.qvd (qvd);
STORE Products INTO Products.qvd (qvd);

Combine them into a single table

Depending on your data model, you can use JOINs and other QV functions:

Orders:
LOAD * INLINE [
    Product, Country, Value
    A, USA, 100
    B, UK, 200
    C, FR, 300
];

LEFT JOIN
LOAD * INLINE [
    Product, Weight, Colour
    A, 10, Red
    B, 50, Yellow
    C, 70, Green
];

STORE Orders INTO Orders.qvd (qvd);

Loading

To load data from your stored QVDs, you simply need to add a LOAD statement to your script, for example:

Orders:
LOAD
    *
FROM Orders.qvd (qvd);

Products:
LOAD
    *
FROM Products.qvd (qvd);

QlikView will then automatically infer the field links as per your original model, provided that the field names are the same as when you stored the tables.