topic Re: Can QlikView store historical data in it? in QlikView

Can QlikView store historical data in it?

Mon, 10 Jun 2013 23:57:36 GMT

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

Re: Can QlikView store historical data in it?

Gysbert_Wassenaar — Tue, 11 Jun 2013 05:55:41 GMT

See these two blog posts:

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.

Re: Can QlikView store historical data in it?

stevedark — Tue, 11 Jun 2013 21:55:13 GMT

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/

Re: Can QlikView store historical data in it?

Tue, 11 Jun 2013 23:58:43 GMT

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

Re: Can QlikView store historical data in it?

stevedark — Wed, 12 Jun 2013 05:24:56 GMT

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/

Re: Can QlikView store historical data in it?

Wed, 12 Jun 2013 05:44:02 GMT

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

Re: Can QlikView store historical data in it?

stevedark — Wed, 12 Jun 2013 22:33:34 GMT

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/

Re: Can QlikView store historical data in it?

Thu, 13 Jun 2013 21:46:58 GMT

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

Re: Can QlikView store historical data in it?

stevedark — Fri, 14 Jun 2013 08:03:02 GMT

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/

Re: Can QlikView store historical data in it?

Mon, 17 Jun 2013 21:14:10 GMT

Thanks so much Steve,

This help a lot.

much appriciated.

Cheers,

Nedums

Re: Can QlikView store historical data in it?

Wed, 30 Jul 2014 19:34:55 GMT

Hi Steve Dark

Could you please explain the or even possibly point out this "watch folder" that you mentioned at the end of your post. I'm interested in understanding this XML file as well. My end goal here is to establish a direct or indirect connection between the QV Publisher and NPrinting Scheduler.

Thanks,

Palak

Re: Can QlikView store historical data in it?

stevedark — Wed, 30 Jul 2014 21:10:48 GMT

Hi Palak,

The help page for this on the NPrinting site is here:

How to Execute NPrinting Reports by External XML Request – Customer Feedback for Vizubi

This is something we have set up for a number of our customers, so please do post back if you need more assistance.

You could use Publisher to fire an NPrinting request simply by scheduling an External Task in Publisher to fire a batch file that creates the .request file for NPrinting. You could also create the .request file from a QlikView reload by loading the required XML into a table in QlikView and using a STORE command to a .request file. Note that the column name of the table would need to be the first line of the XML (as you can not suppress the column header with the STORE statement).

I hope that all makes sense, please let me know if you need further pointers.

Regards,

Steve

Re: Can QlikView store historical data in it?

Fri, 01 Aug 2014 12:46:40 GMT

Thank you Steve. That was very helpful. So if I were to go down the Publisher route, the immediate next step is to create a batch file which I now intend on doing. Again, this was immensely helpful.

Thanks,

Palak

Re: Can QlikView store historical data in it?

stevedark — Fri, 01 Aug 2014 14:11:27 GMT

No problems. Glad I could help.

Steve