Qlik Community

QlikView Documents

Documents for QlikView related information.

Loading Multiple QVD files

Luminary
Luminary

Loading Multiple QVD files

Hi, all of my Qlik applications have QVD data staged and prepared in advance. Here I will go through how I import them efficiently into QlikView.

For now I’ll assume you’re extracting your data each day and incrementally storing them to monthly QVD files.

  • Fact 2014-01.qvd
  • Fact 2014-02.qvd
  • Fact 2014-03.qvd
  • Fact 2014-04.qvd
  • Fact 2014-05.qvd
  • Fact 2014-06.qvd

Lets say the requirement for the dashboard is to have the last two months of complete data plus the current month to date. So if today were the 15th of June 2014 the Dashboard should contain data from 1st April to 15th June 2014.

You could load the data like this (replacing the dates to min / max variables and use dollar expansion):

Example.png

Issues with this approach:

  • We’re using a wildcard on the QVD filename so we’re attempting to load from files we know there’s no corresponding dates.
  • The where clause will also prevent the QVDs being an optimized load.

This is how I would tackle the requirement.

  1. First I’d load %KeyDates from my Calendar (http://community.qlik.com/docs/DOC-6593) using the flags to select the date range required
  2. I’d then load a curser table containing the QVD names I need to load
  3. Looping through the curser table loading the data using a where exists on the %KeyDates loaded in the first step

1) Load %KeyDates

DatesToLoad.png

Here I am loading from my Calendar QVD (you can find this resource on my blog Calendar with flags making set analysis so very simple | qlikcentral and on the Qlik Community http://community.qlik.com/docs/DOC-6593).

DatesToLoadDetail.png

The resulting table has all the %KeyDate(s) for the time period in numeric format. You need to have an identical field in the FACT data as we’ll be using this field in the where exists statement.

We use the QVDLoadSuffix field in the next load statement

2) Load a Curser Table containing QVD Names

QVDToLoad.png

Here I create a new table with distinct values

QVDToLoadDetail.png

This tell us the QVD files we need to look at when loading the data

3) Looping through the curser table

Loop.png

Here we perform a loop reading each row of ‘QVD_Files_To_Load’ (in this example it had three rows as shown earlier; 2014-04, 2014-05 and 2014-06)

The Field Value ‘QVDLoadSuffix’ is read into a variable and that is used within the filename of the QVD we’re loading from.

The where exists ensures only those %KeyDates already loaded (in the first step) are brought back. You can argue on this example the where clause is not required and you’d be correct. I’ve added it to show how we tackle both of the original challenges and it certainly would be required if you were loading the last 90 days (three months) from the current date.

And Finally….

It’s worth noting you can only load optimized with one where exits clause. You can’t use “where exists(field1) and exists(Field2)” without loosing performance although you could combine the fields in your data.

I’ve assumed the Fact Data has the same fields and therefore it will be automatically concatenated. If your data isn’t consistent you could create an inline or autogenerate a table with the complete field list and one row of null values before entering the loop and then concatenate to that. That will also ensure your loads stay optimized (There a useful post which talks about optimization - http://community.qlik.com/docs/DOC-2641)

You can also pass from the QMC a variable that tells the application how many dates to load in Step 1. For example you may want to create a light version with only three days worth of data. Putting Step 1 within a conditional IF statement you can very simply control the load behavior. (I’ll post more about this technique soon)

As always I’d love to hear your comments

Richard

qlikcentral | Understand / Create / Inform.

Comments
Not applicable

how I can get the name of qvd?

0 Likes
Luminary
Luminary

Hi Angel,

The name you would know as this just deals with QVD's split into monthly files.

You could change that to a variable and use a for each loop around the whole of this code if you wanted to repeat the process for many fact tables......

Richard

Not applicable

Thanks Richard,

I need the name to save the file again with additional fields.

Luminary
Luminary

Ah I see, try this line:

Store Fact in 'Fact $(vQVD_Date_Suffix).qvd' (qvd);

Richard


0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2014-06-03 05:13 AM
Updated by: