In part three of my Back To Basics series of QlikView blog posts I look at QVDs. What they are, how to create them and why you should be using them in your QlikView project.
QlikView QVDs play a crucial part in the majority of QlikView implementations. As such I have blogged about their use many times previously; such as in this post on Incremental Loads and this one on Optimised Loads. Now that I am going Back To Basics in this series of posts I felt it was a good point to go back to first principles with QVDs.
What Is A QVD?
At a basic level a QVD is a flat data file with the extension .qvd. It can store a single table of data (is. each row shares the same column list) and is typically created in the load script of a QVW file. The structure of the file is essentially an XML format, with various bits of header information stored at the top of the file and the rest of the data beneath. One of the beauties of a QVD file is that it is compressed on creation using the same algorithms as QlikView uses to store data in memory – so the files can be incredibly small for the amount of data they contain. Loading from QVD back into memory is blindingly fast as the format of the file mirrors how QlikView addresses data in RAM.
Why Should You Use QVDs?
One of the points that is often noted when pitching QlikView is that it does not need a data warehouse or predefined data cube to work off. This is completely true, but the requirement to have a logical data layer often remains. This is particularly true if the data being collected is coming from multiple systems or is being distributed via multiple QlikView applications. QVDs can fulfil this requirement admirably. QVDs are also essential when you want to adopt an incremental load strategy.
Personally I would go as far as saying your implementations should always be built on QVDs – except perhaps where the data source is a simple Excel spreadsheet that is stored locally. There are many reasons I would suggest this, some of these are:
- Decoupling data extract from data presentation
- Ability to do parallel data extracts
- Easier unit testing of parts of the load process
- Incremental loads
- Sharing of extracted data between presentation apps
- Ability to scale the solution when data volumes grow
- Ability to delegate responsibility for different parts of the data load to different teams
The question you should be asking when designing your data load strategy is not why you should be using QVDs – but rather are there any reasons why you shouldn’t be using them (the reasons here are very few).
How Do I Create A QVD?
Typically QVDs are created during the execution of your QlikView load script. A STORE statement writes the current contents of a single table in your data model to a file on the disk. The syntax for this is:
STORE TableName INTO ..\Data\FileName.qvd (QVD);
Note that the STORE command can also be used to write data into a comma or tab delimited file – but that is a topic for another blog post.
QVDs can be created during the execution of any QlikView load script, but best practice is to have separate applications which deal solely with the creation of QVD files. This “extraction layer” then handles all interaction with source databases. Depending on the size and complexity of the data being loaded I may create one app for each table being extracted or a single app may create all QVDs for the solution (or anything else in between). What is important though is that the front end can be reloaded quickly from locally stored data files. This ability to refresh the presentation layer quickly can massively speed up development.
How Do I Use My QVDs?
Once you have created a data layer consisting of a number of QVDs you simply load from each QVD file in the same way you would a CSV or Excel file. The syntax is:
TableName:
LOAD
FieldList
FROM ..\Data\FileName.qvd (qvd);
Note that where with CSV or Excel files the load statement contains a chunk of information about file formats this is not required with a QVD load. You can also use the wizard in the load script by clicking the File button and locating the QVD in the folder browser.
When loading from a QVD you can apply a number of transformations, such as renaming columns, excluding rows and adding derived columns. Be aware though that many of these transformations will cause your QVD load to be non-optimised (which will make the load up to 100 times slower) please see this blog post for more information: QlikView Optimised Loads.
By loading from multiple QVD’s into a single application you can build up your associative data model. This could involve some data from QVDs (perhaps originally from different data sources) and maybe some small lookup type data values from yet another source.
As well as being a very quick way of loading data into QlikView apps QVDs can also be useful for archive – due to the excellent compression which is used in their creation. You could, for example, store dated copies of a data set into QVDs; your front end would then typically work off the latest version, but it would be possible to point it to an archive version of the data if required.
Conclusions
QVD’s are QlikView’s proprietary way of staging and storing data. They can be used to provide a logical data tier in your solution and in some cases could remove the need for a data warehouse. Using QVDs you can share data between multiple applications without having to keep going back to the source data, you can also chunk up the data load process into manageable steps.If you are presently using QlikView but not using QVDs then I strongly recommend that you consider the advantages of doing so.
Thanks Steve . Good post.
Regards,
Durga
Thank you for the article. I am trying to create a QVD using multiple tables from the same database and JOIN statements. Unfortunately none of the joins are working properly. Any advice in how to get them to work properly?
Hi Heather,
How joins work depend a lot on what types of data you have in the joining fields. Things to remember are that all fields with matching names will be used in the join keys, left joins require all values to exist in the first table loaded, field matches need to be exact (case, rounding etc.). Try loading just the join keys into a single concatenated table and see if you get the values you expect in list boxes.
Often ApplyMap is a better route than a JOIN statement, sometimes doing the join in the SQL statement works (but be careful of sending too much duplicate data over your network) and occasionally keeping the tables separate in the data model and allowing QlikView to associate in the front end is the best route.
You will find other articles on this blog relating to data modelling which may help with designing your data model.
dear Steve,
i DO agree with everything you say.
What can be a good solution, if we want to keep optimised load with the need to use Autonumber function in the script.
Indeed, it’s impossible to use Autonumber function and store the result in the QVD and of course, if we use Autinumber function in the load script, we lose optimised load :(
Joh
Hi Johann,
Because of the fact that the auto number sequence is reset for each new load script run (so persisting to QVD does not work) I very seldom use the AutoNuber feature.
Steve
Thanks for you answer Steve, and once again i do agree.
But, how can you have Integer type fields for your key everytime?
Best regards.
Johann
Hi Johann,
The simple answer is that I don’t. Whilst a smaller key is going to be slightly more efficient than a larger one (sequential integer from 1 being the optimum) it really isn’t going to make that much difference to performance. I have some apps with very long keys in use. If I want to slim down a key, such as a composite one with many parts, then I may chose to use the Hash128 function to make the key smaller.
Hi Steven,
So if I read this correctly in order to use QVD files in the “master file” I have to make a QVW file for every excel file and then store it as QVD. So basically having everything double?
John
That is correct. If you have simple Excel files where you don’t need to do lookups etc. then it is probably not worth worrying about QVDs. If you are connecting to a more complex or large datasource the QVD is a file you can quickly refer back to many times – without dealing with a slow data load process.
Hi Steve,
In the ‘why’ section I’d add: the opportunity to take data off-site during development.
This allows you to continue development while out of reach of any database. (Although for most QlikView projects this will not be much of an issue, in our case this can come in quite handy.)
Regards,
Martijn
Hi Martijn. I completely agree with that. I will sometimes use a QVD generator that enumerates around all tables in a database and create QVDs of them in their original format. You can then develop from them and know it is a simple job to re-point back to the database later on.
Hi Steve,
Thanks for a great blog
There is a good way to load qvd files back to SQL srv?
Regards,
Almog
Hi Almog, thanks for the comment on the blog – glad you like it! There are very few products other than QlikView that will load QVD’s. To import back to SQL Server you will want to go with a CSV or other delimited text file. These lose some of the advantages of QVD’s, but are transferable between systems. To create a text file output you still use the STORE statement, just replace the (qvd) with (txt), there are additional parameters you will be able to find more details of on-line.
Thanks for your answer,
The thing is that I wondering if it will be a good idea to store QVDs that will serve as DWH.
The same qvds have to be read by QVW docs and by some Other Reporting tools.
Is it will be effective to store all the data to QVDs as well to txt files (qvd for QV, txt for riporting tools)
Or it will be better use one of the products that loads QVDs?
Many thanks,
Almog
For data that is destined to be shown in QlikView all you need is a repository of QVD files. Generally there is no cause to load data back into a database as users can get all the reports they need from QlikView. If you need to read some output into another system then a dump to text file from QlikView may be appropriate – but there are other tools for moving data between systems that may do a better job of it.
hi steve,
i am new to qv.
can i connect sql server and oracle via oledb and odbc respectively in the load script.please explain in detail
The load script is executed sequentially, so you simply have to connect to one then pull your data from there, then connect to the other and get your data from that source. However, what I would recommend is a separate QVD generator for each source that writes out to QVD. The presentation document can then load its data from the QVDs. This means if one database is down the other still refreshes.
[…] QViewer is a quick and lightweight (under 200KB) file view that stands alone for analyzing the QlikView’s QVD files. Debugging and developing applications can be time consuming and drag down your […]
hi steve,
This blog very good.
i have one doubt about qvd’s.when i load qvwfile into qvd’s the compression will done.
can you explain me with example.
Thanks in Advance,
praveen kumar
Hi Praveen,
When QlikView has a table in memory the data in it is compressed (mostly by de-duplication of values in columns). When an in memory table is written to QVD with a STORE statement this file then has the same compression on it. If you load a table in a load script and then issue two STORE statements, one to CSV and one to QVD you will see the difference in file size quite clearly.
Steve
Hi Steve,
I still don’t get why we use QVD’s in the first place.
Lets say I load data from an rdbms to a qvd file.
Next I would a Qlikview table from the qvd file.
Now, if I had directly loaded the data from the rdbms onto the Qlikview table, wouldn’t I incurred either the same or may be slightly less cost (as the indirect approact is 2 stepped).
My guess then is QVD’s are useful only if I have to Join the QVD’s with other Qlikview loads right?
Ravi
Actually, going back and reading this post, I do get one use when the same data needs to be SHARED between multiple applications or even in the same application.
Instead of querying data from the rdbms several times, I guess I can do a one time load to a QVD and load then load data from that qvd multiple times without going back to the rdbms right?
Hi. That’s exactly right. You can query the RDBMS once and push the same data into QlikView multiple times. This is even more true when incremental loads are done, so only recently changed data is loaded from the database, whilst all historical data is loaded from QVD. I have written a separate post on Incremental Loads in QlikView that you may find helpful on this topic.
Hi,
My question is what is the use of writing (qvd) after filename.qvd
ex: abc.qvd (qvd)
I was able to create qvd without using the (qvd) after the .qvd. What is the difference?
There are other formats that can be written (see my post on the STORE statement) but QVD is the default so the statement works without the suffix. I would still add it on though.
Hi Steve, great tutorials you have here.
I am new to qlikview, and want to understand something.
In my current process I:
1) I load .csv files
2) I store those in .qvd files
My doubts/questions are:
I want to monthly add data to the .qvd with new csv’s:
* In the edit script I first LOAD the .csv, then STORE into .qvd, but by adding the data to the same .qvd replace the existing information or append the new?
And I want the .qwv to use the .qvd
* How I do that?, by LOAD in the edit script?. Do I have to add a TAB in the “edit script” for that?
Thank you very much for the information!
Regards,
Andrés.
Hi Andrés. The answer, as it often is, is that it depends. If you have monthly CSV files, with a date suffix on, I would create monthly QVDs with the same suffix. This removes the risk of doubling up or missing rows of data. I have another post on dealing with multiple CSV Files which may give some ideas on that. If you do want to append into one QVD you simply load the CSV first, then load from QVD with a CONCATENATE or auto-concatenate by having exactly the same field list (or both), again there is a post on incremental loads that may help. To use your QVD’s simply load them in the Edit Script of your QVW. I would suggest separate tabs for each table of information you have in the QVW – but this is only for cosmetic purposes (and to make future editing easier). A QVD is just another data source – much like a spreadsheet or CSV file.
Hope that helps.
Hi Steve,
I am beginner in QlikView. As QlikView is basically reporting tool and it does not require datawarehouse,then how we can do reporting ? can you tell me relation between being a reporting tool and not requiring any datawarehouse unlike other MS BI tools ?
Hi. QlikView can pull data from many different data sources. It can load directly from a database, flat files (like Excel) or from on-line services such as SalesForce. You can build apps straight on top of any of these sources. If you have a datawarehouse in a database you can pull data from there, but if you don’t you can just go to your OLTP database. Hope that makes sense?
hi Steve , i am a beginner in Qlikview. i would like to know that how can i read the qvd in my extension object using javascript.
Hi Abhick,
This is not something I have ever attempted, and given the compression in the file I think would probably be impossible. What you need to do is load the data from the QVD into the data model and from there you can refer to it in your extension.
Hi Steve,
I am new to qlikview and also qvd’s, cn you please help me out in the step by step creation of the qvd for a data that is too huge to handle. I need a step by step process please.
Thanks
Hi Kirtya,
This article includes step by step creation notes. For huge data sets you should see my post on QlikView Incremental loads also ( https://www.quickintelligence.co.uk/qlikview-incremental-load/ ).
Key considerations are to only bring the data you need, exclude everything else. Also, try and break things into chunks – a simple incremental approach is to have a separate QVD per day or per month. Also, try and keep your data model simple – as close to a single table as you can – again I have produced blog posts on this topic.
If you can not find what you need here Qlik Community and AskQV.com are good next stops.
Hope that helps!
Steve, This is very helpful
How do you schedule the qvd to update on a regular basis like every Sunday morning at 1:00 AM?
The most common route is to schedule the refresh of the QVW that creates the QVD through QMC. If you don’t have QlikView server you can use Windows Scheduler and a batch file. Let me know if you need further pointers.
I know this may seem like an odd question, but is there a way to load specific fields from a table to QVD without storing the entire table or scripting a separate LOAD?
Hi Oliver,
You can simply specify which fields you want to store in the STORE statement itself, like this:
STORE Client, Type, Who FROM Billable INTO Billable.qvd (qvd);
Hope that gives you what you need.
Steve
Very good post. How to edit .QVD file ?
Typically you don’t edit QVD files. You simply have to load from them, whilst making modifications, and then write back over the top of the original file. I believe the product QEye has some edit functionality, but I have not tried to use this feature.
Hello Steve,
I’ve been trying to load a QVD file since this morning but i can’t get it to work. It says that the QVD file cannot be read.
Could you please tell me the possible reasons why the loading of a QVD file could not work ? It would really help to know where to start looking.
Thank you for your help
Hi,
This can happen if the file is not actually a QVD (i.e. another type of file with an incorrect extension) an empty file (i.e. zero bytes of data) or if the file has got corrupted. Depending on the size of the file you are trying to read, you may be able to load it into a text editor – the start of the QVD should be XML which will give you a chunk of information about the file. If it is of a different type then you will not see the XML here, similarly a zero byte file will be obvious. If the file has got corrupted then you may have problems. If the file is generated from scratch each time then you can simply try regenerating it – obviously this is less easy if you have an incremental load in place. Where you have an incremental load (that is not possible to create from scratch in a sensible amount of time) it is critical that a backup of QVD files is taken.
Hope that helps.
Steve
HI,
I am New In Qlik Sense , I want to create QVD and develop the application through the Stored Qvds,
i really want how to update data into Qvds on regular basis.
but when i load my application it actually uploaded from SQL connection to end of the project, then how can i ignore the part of Fetching Data From SQL to Qlick Sense because i want to set the job for the time interval and it fetching the data from SQL and updated into Qvds.
You will need to create one Sense app that talks to SQL Server and writes a QVD and then another one that reads from the QVDs and doesn’t talk to SQL at all. When you set up the reloads the one that talks to SQL needs to be scheduled at a time interval and the other (with the charts) can be chained to run after that initial reload. It is the same as QlikView in that respect.
Hello Steve,
I am tryng to load all .xlsx archives in my directory, but i have a problem with temporari files “~”
Below follow my code:
//// CARGA DA PLANILHA ***********************************************************************************
//[Sheet1]:
//LOAD Date(A) as Data,
// Date(B,’hh:mm’) as Tempo,
// C as Tipo,
// D as [Sistema-Produto],
// E as Projeto,
// F as Entregavel,
// G as [Numero-CA],
// H as Tarefa,
// FileBaseName() as Recurso,
// Text(Date(A,’YYYY’)) as Ano,
// Capitalize(Mid(Month(A),1,3)) as [Mês Abreviado],
// Text(Date(A,’MM’)) as [Mês Numérico],
// Text(Date(A,’DD’)) as Dia,
// Num#(Date(B,’hh’)) + (Num#(Date(B,’mm’))/60) as Tempo_Horas,
// Text(Date(A,’YYYY/MM’)) as [Ano/Mês Data],
// ‘T’ & Ceil(Month(A)/3) as Trimestre,
// J as idSemana
//
//FROM
//[\\…..\Data\Files\*.xlsx]
//(ooxml, no labels, header is 1 lines, table is [Atividades])
//WHERE( A ‘Data’ AND
// A ‘Total’ AND
// B ‘0:00’ AND
// Len(C) > 0 AND
// Len(F) > 0 );
//STORE [Sheet1] INTO C:\Users…\Data.QVD ;
Coud you help me to read all archives .xlsx discarding temporary files ?
Regards,
Hi Ivan,
This is a problem that I have come up against in the past. It can be avoided by doing a loop around the files using a FileList, rather than loading from a wildcard. You can then add an IF statement that avoids files with a ~ in the name. If you look at this blog post you should get the idea https://www.quickintelligence.co.uk/convert-drop-folder-files-qvd/
Hope that helps.
Hello Steve,
Thanks a lot for your help,
I am trying to do in follows way:
for each vFile in FileList(‘\\…\Timesheet\*.xlsx’)
if index(vFile, ‘~’) = 0 then
TableName:
load
Date(A) as Data,
Date(B,’hh:mm’) as Tempo,
C as Tipo,
D as [Sistema-Produto],
E as Projeto,
F as Entregavel,
G as [Numero-CA],
H as Tarefa,
FileBaseName() as Recurso,
Text(Date(A,’YYYY’)) as Ano,
Capitalize(Mid(Month(A),1,3)) as [Mês Abreviado],
Text(Date(A,’MM’)) as [Mês Numérico],
Text(Date(A,’DD’)) as Dia,
Num#(Date(B,’hh’)) + (Num#(Date(B,’mm’))/60) as Tempo_Horas,
Text(Date(A,’YYYY/MM’)) as [Ano/Mês Data],
‘T’ & Ceil(Month(A)/3) as Trimestre,
J as idSemana
from [$(vFile)]
(ooxml, embedded labels, table is Sheet1);
ENDIF
next
But work.
I dont know if “if” conditions is right, coud you help me ?
Regards,
The IF statement looks sound, but the path in the FileList is incorrect, you can’t have \\ without a machine name or domain following it. Use the Debug mode in the load script to check what is going on – but I’m reasonably sure you won’t be finding any files with \\…\Timesheet\*.xlsx
Thanks again!
Please, let me ask you somethings ?
1- what is this significate: “if index(vFile, ‘~’) = 0 then” ? The word Index, is referente to file name ?
2-When i put something diferet of this: “‘$(vFile)’ as SourceFile, * ” on field LOAD, the script does not work!
How do i do this FOR LOOP load the fields of my xlsx ?
Follow my script:
for each vFile in FileList(‘\\srv01\Timesheet\*.xlsx’)
if index(vFile, ‘~’) = 0 then
GestaoAtividades:
load
‘$(vFile)’ as SourceFile,
*
from [$(vFile)]
(ooxml, embedded labels, table is [ApontamentoAtividades]);
ENDIF
NEXT
Sorry to be so repetitive, but i dont know what is wrong.
Regards,
Index is Qlik function that returns the location of the second parameter within the first parameter, for instance index(‘find~tilde’, ‘~’) = 5. A result of zero means the character ‘~’ has not been found.
If * works but a field list doesn’t I would expect that not all of your source spreadsheets are of an identical format, with identical sheet names.
Debug mode will be your friend in identifying what the problems are.
Hello Steve!
I made this work after some adjusts!
Thankyou very much for your help!
Follow my script bellow:
for each vFile in FileList(‘\\srv01\A\Time\*.xlsx’)
if index(vFile, ‘~’) = 0 then
[Equipe]:
LOAD
A ,
B ,
C ,
D ,
E ,
G
from [$(vFile)]
(ooxml, no labels, header is 1 lines, table is [Equipe])
ENDIF
next
Kind regards!!
Hello Steve,
Thanks a lot for the post.
I am very new to Qlik. I have requirement see the source table reference in a QVD file. I only know how to use existing QVD file in new script. Can you please help on this?
Best Regards
Hi Chami,
Please see https://www.quickintelligence.co.uk/read-meta-data-qlikview-qvd/ and https://www.quickintelligence.co.uk/easyqlik-qviewer-3/ – this will give you two different routes to discovering where the data within a QVD was originally loaded.
Hi Steve,
Thanks for these details.One questiono here..if i copy all these QVD files along with corresponding QVW files on my local machine from server and if i run it again in order to update QVD data then will it hit the database or sap query or it will simply keep earlier data.
Hi Dipak. Typically you would split the load into two parts. One QVW would purely get data from the SAP database and write it to the QVDs. The other would load data from the QVDs into the front end application. If you copy all of the files to another location, you can reload just from the QVDs by running the load in the front end application. If you wanted to refresh the QVDs you could run the load in the QVD generator QVW. You would need to ensure you had connectivity to the database from the machine you copy to in order to do this though. Hope that helps.
Thanks Steve for the explanation.
Hi Esteve,
Firs of all, thanks for your posts !! They are very helpful.
You say that qvd files typically are created in the load script. But I have a question about storing aggregations in qvd files:
If from the data extracted from the data source in the load script, I need to perform different calculations (aggregations, etc.) and after load them into the qvd, how could I do it?
These calculations would not be done in the script, they would be done in expressions.
And these loads should be incremental (every week) in the qvd file.
I’ve explained ?
Thank you very much for everything.
Hi,
There are many expressions that have to be done in the front end, because they are based on selections. Some can be done in the QVD however, which will make the front end quicker. For example, if you have FXRate and LocalValue in a table you can create FXValue in the load script.
The aggregations I mention are to create a table with a smaller number of rows than the source. For example, if you have a table with many rows for each day, but you only need to know about monthly values for the analysis you are doing you can do a GROUP BY to the Month and then Sum any values across the multiple rows. This group by Aggregation could be done in a QVD Generator and the smaller leaner QVD could be written out, or it could be done in the load into the main application.
Hope that answers your question?
Thanks so much for the article.
I have decided to implement my project with QVDS after reading it.
My original project has been working well for sometime and only consists of three basic tables.
Each table has numerous loads in the project pulling data in from different data sources.
To implement and move to loading from QVD’s I have placed a line for each of the three tables at the end of the package.ie …..STORE ComboValue INTO C:\temp\ComboValue.qvd (QVD);
The QVDs are populated and a secondary presentation project is loading data from QVD.
My question – Not all data has come across.
In one table I have 5 categories or data values – Only 1 of the 5 has come across.
What might I look at where not all the data is being loaded into the QVD file?
I am using the personal edition so wondered if it was licensing related ?
Hi Shaun. The debug mode in the load script can be very useful for capturing a number of things, suggest using this to step through your original code. Are there loops in your original load? If so, are the QVD writes at the end of the loop and not the beginning (i.e. is each category being written out in turn, and only the last one persisting). Checking with EasyQlik QViewer as the load is taking place will allow you to see what is going on. Make sure, with the table viewer, that there are indeed only three tables in the data model of the original app – and that one of them has not split into many tables.
Hope that helps.
Thanks Steve.
Looking at the table viewer there are Synthetic keys involved that have created synthetic tables in the source package. The script itself reads data into only 3 tables but there are a few synthetic tables.
Would that be the cause of my issue and how would you recommend dealing with synthetic tables when loading data into QVDs ?
I can’t see why the synthetic keys will be creating the issue that you are talking about with the QVD – but it is well worth sorting them out anyway. There are plenty of articles on-line on doing this. Simplest way is to remove fields that are causing incorrect joins, next simplest is to rename fields (with as) and then finally (if required) create composite keys. Again, there is information on line on all approaches. Good luck!
Sorry Steve one last one as I still am only getting limited data writing into the QVD although the source package loads all records.
When I Load data I use following structure to load data from multiple sources into the one table.
TableName1:
LOAD.. FROM 1
LOAD.. FROM 2
LOAD.. FROM 3
STORE QVD
TableName2:
LOAD.. FROM 1
LOAD.. FROM 2
LOAD.. FROM 3
STORE QVD
Do you need one single STORE per table or one single STORE per load ?
Got me staggered why the QVD is not populated with more records.
That looks fine, as long as 1, 2 and 3 are loaded into the same table in the source app. This will only happen if the three tables all have identical structures. If they don’t only 1 will go into Table1, the others will go into a different table and you will only store the first part – this will also cause massive synthetic keys. Try looking up CONCATENATE and AUTO CONCATENATE for more info on this behaviour.
Hello Steve:
My apologies if I missed this up above. Nice article by the way.
After reading this, I believe I’ve had an Ah Ha! moment. Currently, my load script is structured like the following:
[Sample1]:
Load Field1,
Field2,
Field3,
.
.
.
Field10;
SQL Select Field1,
Field2,
Field3,
.
.
.
Field10
From database.name;
Store Sample1 into ..\QVDs\Sample-Data.qvd (qvd);
So, it’s doing a full load every day and storing it into the QVD file. What I believe I’m understanding from your article and other comments, is that I can take the Load section out of the above and just have the SQL section and write the data to the QVD file. Then I would need to add a separate Load section without the SQL code and end it with a From line. So the new Load section would look like the following:
[Sample1_Load]:
Load Field1,
Field2,
Field3,
.
.
.
Field10
From ..\QVD’s\Sample-Data.qvd (qvd);
Is that right? If yes, would I really gain anything by doing that in terms of load time? Thanks in advance.
In the example code that you paste up top both the preceding load (the LOAD…) and the STORE statement are achieving nothing.
The purpose of the preceding load is to do manipulations of the data in QlikView over and above what you have done in the database – this may be translating a date field into parts, splitting strings or looking up values in a lookup table. If your LOAD field list is identical to your SELECT then it is doing nothing.
Similarly, the reason for storing to QVD is to then use it elsewhere – which you are not presently doing. Whilst storing the file and then loading from it in a separate LOAD statement is using the QVD (be very careful not to duplicate every row of data doing this, by the way) it’s not giving you any advantage, and you are actually slowing your load script rather than gaining anything. The reasons for writing to QVD are to load from the database once and then load that data into multiple QlikView apps (without hitting the database again) or to do an incremental load of some kind. Generally it is good practice to put the creation of your QVDs into a separate app to where you analyse the data.
To illustrate the use of preceding loads and incremental loads I would recommend these two further blog posts:
How To Use Preceding Load
QlikView Incremental Load
Hello, is it possible to import QVD file via REST connector?
REST allows XML but I havent figure the right way to import the right data into QlikSense.
Regards
Matias
You would need to write an endpoint which read the QVD and then exposed it as an API. This would be extra effort and remove most of the advantages of using QVD files. Generally you would just set up a share to a folder and create a Sense connection to that. Is there a reason why this is not possible for you?
[…] for writing QVD files during load scripts. If you don’t do this then you should probably read this previous post on the topic. What many people don’t realise though is that there other values that can appear […]
My computer has 4 Gb of RAM, It runs windows 7 64 Bits. My data source is a 6.67 Gb .qvd file.I would like to split this file into a smaller one in order to develop an application in qlik sense. In any attempt to access this file my computer crashes.I would like a hint on how to generate a slice of that file, without having to load it completely into memory to avoid overloading my computer.
Hi Maxwell,
You should be able to load using a where exists and then save that slice. So, to get all rows for a Friday (for example) you would have:
tmpDays:
LOAD
WeekDay
INLINE [
WeekDay
Friday
];
MyData:
LOAD
Date,
WeekDay,
Sales
FROM [MyBigQVD.qvd] (qvd)
WHERE EXISTS (WeekDay)
;
DROP TABLE tmpDays;
STORE MyData INTO [SmallerQVD.qvd] (qvd);
DROP TABLE MyData;
Choosing only the fields you absolutely need will help. In order for the WHERE EXISTS to be super efficient ensure that the field you do the exist on is in the column list. I’ve done a post on Optimised QVD Loads that goes into this in more detail.
Hope that helps.
How is the performance of SUM, GROUP BY query on a QVD file ?
Scenario:
I have 4 QVD file consisting of lower level of granularity (i.e. order no) each pertaining to a different subject area and have 4 corresponding QS Apps.
Now I want a management dashboard which should have data from all 4 qvds at higher granularity(i.e company level). Hence thinking of running a SUM, GROUP BY query on all 4 QVD and merge them using a linked table. I wouldn’t want to load detail level data for a management dashboard, it would also be redundant and consume too much RAM.
Hi,
That is a good solid approach. Group By and Sum will work fine on a QVD, but will potentially be slower than simply loading from the file – as the load will not be optimised.
Performance of the dashboard will be improved if when you aggregate the other QVDs you look at getting them all as consistent as possible and concatenate them in the management dashboard – rather than using a link table.
Where you can get great performance gains is by using many QVDs and working over them one at a time. For instance, if you have a monthly lower level QVDs when you come to create the aggregated QVDs you only have to process recent months, the old ones can be ignored as you will already have aggregated files for those.
Hope that helps!
Awesome, thank you!
I have to use Link tables, as I have a Section Access that depends on 3 dimensions and some difference in granularity of KPIs from QVDs.