Can QlikView store historical data in it?

[#1]

Hi,

I’m new to QlikView and trying to understand how it works.

I learnt that in QlikView the data can be stored in QVD files and then used in reports.

How often these QVD files get refreshed? And is it controlled in Qlikview Server??

Can user manually trigger a refresh or it has to be scheduled?

Is this qvd files work as an interim database? And can we hold the historical data in this qvd files?

That’s is if I’m refreshing the qvd files monthly inserted of overwriting the existing data can we append the new data to the existing one.

(for eg if we don’t store the historical data in the source table can we add a new field in the qvd files and add the period (month) to the new field so next time we refresh the data new data will be appended  with new period so we have the historical data in qvd and create trend reports. Is this possible in qlikview or should we create this in a sql database and then connect to it to create reports?

Your help would be much appreciated.

Thanks

Nedums

[Gysbert_Wassenaar]

See these two blog posts:

• Perfect Your Data Model
• QlikView Incremental Loads

I think those will answer a number of your questions.

You can refresh your qvd's as often as you want (as long as they can be generated fast enough, i.e don't expect 10GB of data to be stuffed into a qvd on a 30 seconds schedule). You can schedule qvw's in Qlikview Server to generate qvd's and reload the qvw documents.

[stevedark]

Thanks for sharing these links Gysbert, it's nice to know my posts are read and appreciated!

Regarding the orignal question, QVD's are not quite like a database, but they are a data repository - one that can be read from at incredibly fast speeds.

This means building a new database is not required, and you can append new data to historcal in exactly the way you describe.

My blog also has a post on Optimized QVD Loads, which may also be worth reading.

Steve

http://www.quickintelligence.co.uk/qlikview-blog/

[#4]

Thanks Gysbert for sharing these useful links.

and Thanks Steve for the posts.

I'm new to QlikView and now I'm getting there.

Can we accept a user parameter while running the script and add this as a new field in qvd file?

For eg: I wanted to get the period (month) as a user input and add it as a new column along with other data in qvd file. Is it possible to accept a user parameter while loading the data??

Thanks in advanced

Nedums

[stevedark]

There are various ways of accepting user input, and for adding data to QVD

files. Generally it is a good idea not to add to QVD files when loading

from them though - due to how optimized loads work.

What is it you are trying to achieve here? I'm sure someone will be able to

suggest a solution.

Regards,

Steve

http://www.quickintelligence.co.uk/

[#6]

Hi Steve,

We have data from 3 different source (financial data, Risk and Budget data and project schedule data) one (financial) has historical data in it other 2 don’t. so every month we need to take a snapshot of the data where we don’t have history and add a month that snap shot is taken and store them . then we need to create the reports using these 3 data sources.

The joining key for these data set is Project no and the period (month) (for last 2 dataset we will use incremental snapshot as the data source for report so we have the joining keys.

Initially we thought to create a database in SQL to store the snapshots. But as Qlikvie qvd’s can do the same job thought to eliminate the sql server.

Could you please advise me which is better ? (using SQL or using QVD)

Secondly we need this data load happen in 2 ways automatic (via a schedule job) and manually.

For the automatic one we can get the current month and add it as the period for snapshots but for manual one we would like to pass a parameter (because if one wanted to reload the data on 1^st of the month but for the previous month we need to pass this as parameter)

I have searched and found we can get user input using

Let vPeriod = Input('Enter Period' , 'Input required'); 

But as I’m new to this not sure this approach is a good practice.

Could you please suggest me a good solution for the above mention problem.

Also can we pass parameter from command prompt to qv.exe file?

When run the qvw file from command prompt (qv.exe /r myfirst.qvw) it waits for user input (vPeriod) can we pass this as a parameter via command prompt.

Your help would be much appreciated.

Thanks

Nedums 

[stevedark · Accepted solution]

The best approach for incrementals depends on the volume of data and how long it takes to pull down.  If extracting whole months is okay and the date stamped on the row can not change then QVD's with the month stamped in the file name (as you describe) makes a lot of sense.

If you want the scheduled reloads to always do current month, but have the ability for a manual reload to refresh a QVD for a past month, I would do this with a parameter that can be set in QlikView desktop.  You could use something similar to the date pickers in this example - but modified to select months rather than dates:

http://community.qlik.com/docs/DOC-4314

You can pass variable values via the QlikView command prompt  (eg. qv.exe /r /vvMonth=Jun13) but this would require manual intervention anyway and couldn't be done with QlikView Server (Publisher has more options around parameters).

You can also pass in parameters via config files.  Typically if I am doig this I will put the paramters in a CSV file and load them in as a temporary table.  You can then use the peek function to get the value into a variable and then finally drop the temporary table.

If you can get your QVD layer right then the extra effort required in setting up a SQL Server is certainly not required.

Hope that helps,

Steve

http://www.quickintelligence.co.uk/

[#8]

Thanks Steve,

Now I have much better understanding.

Can you please explain how to pass variable in command prompt bit more with an example (script sample). On your example (eg. qv.exe /r /vvMonth=Jun13)

You are passing vvMonth as a variable, but I’m bit confused how we receive this in the script. Do we need to declare a variable in the script with the same name? eg  SET vvMonth = ‘’;

Or should we declare it as user input eg Let vvMonth = Input('Enter Month' , 'Input required'); 

In my case data is not huge is we can pull it quite easily.

But is there any advantage or disadvantage storing historical data in QVD compare to storing it in SQL.

If it is in SQL we can we can manipulate or view it with different applications but if its in QVD we can handle it only in QlikView is that right?

Also can we load data to QVD using a web service? Is it possible?

Thanks

Nedums 

[stevedark]

Hi Nedums,

In the command line syntax the switch for passing a parameter is /v and the variable name is just vMonth - I appreciate this is not clear when you just see the syntax.

If passing a variable in this way you must not set it in the load script - as the script input will overwrite the command line input.  You will however need to create the variable for it to be passed in.  This can be done within QlikView under the menu items Settings \ Variable Overview .

Because you may want to use different variables based on whether it is a standard run or a command line one you probably need a couple of variables (eg. one that is always the parameter, and one that is requested from a prompt) and then use if Logic to decide which to use.  By resetting variables at the end of the script you can check to see if one is provided, eg.

if vMonthParam = '' then

  let vMonth = Input('EnterMonth', 'Input Required');

else

  let vMonth = vMonthParam;

end if

You may also find it helpful to check the value of OSUser() function to see if it is your system account or another user that is running the script.  You can then take different actions depending on this.

On you other questions, if you have other uses for having the data in SQL then it is worth creating the database and using that for QlikView as well.  If the data is only read into QlikView then a QVD layer will be fine. There are very few other apps that can read QVD's.

You could write a web service to fire a QVD reload.  Natively QlikView uses EDX to trigger loads, you can search for more information on this in QlikCommunity.  You could write something to fire a command line execution of qv.exe.  There are also third party tools, such as NPrinting that can help - as this has a watch folder where you place a reload request (a simple XML file) and that fires a reload.

Hope that helps,

Steve

http://www.quickintelligence.co.uk/

[#10]

Thanks so much Steve,

This help a lot.

much appriciated.

Cheers,

Nedums