topic Database Selection on Model Open? in QlikView

Database Selection on Model Open?

Tue, 04 Jan 2011 12:15:04 GMT

We are in the fruit industry and have recently started using our 2011 database from a pallet tracking system.

This makes it kind of difficult to switch between 2010 and 2011 data in Qlikview.

Now i have my 2 load scripts running in the background collecting data from both databasis and storing it as QVD's with 2010 or 2011 in the file name.

I have one model that have all my reports etc tho which loads the QVD files that i have specified.

Is it possible to have a 2010 or 2011 popup when the user open the document and based on that selection the model will load the 2010 or 2011 QVD's automatically?

Database Selection on Model Open?

suniljain — Tue, 04 Jan 2011 12:19:03 GMT

you mean, you want to load data according to user selection. i.e 2010 or 2011

Am I right ?.

Re: Database Selection on Model Open?

Miguel_Angel_Baeyens — Tue, 04 Jan 2011 12:22:40 GMT

This script will do:

LET vYear = Input('Input year', 'Warning!'); 
Data:LOAD *
FROM File$(vYear).qvd (qvd); // provided the file is File2010.qvd

Hope this helps.

Database Selection on Model Open?

Tue, 04 Jan 2011 12:38:05 GMT

yes, my load scripts already load and store the 2010 and 2011 QVD files seperately, but i dont want to use 2 different models to view the data, my one model is like a template to with all the reports, i just want a "switch" to select which set of QVD files to load

Database Selection on Model Open?

suniljain — Tue, 04 Jan 2011 12:40:34 GMT

pls check in shared document , someone alredy posted application for this types of requirement.

Database Selection on Model Open?

deepakk — Tue, 04 Jan 2011 12:56:13 GMT

hi Ruan,

I would recommend you to make it simpler by loading both the data into the application.

Provide a filter of Year to user so that user can change the year on their will.

YOu can by default select the Year 2011 on the open Event of the application hence when ever the user opens the application he will be able to see the 2011 data and will have the option to change it to 2010 uisng the filters

Database Selection on Model Open?

suniljain — Wed, 05 Jan 2011 07:06:53 GMT

suggestion given deepak direclt affect the performance at design level. to give fileter based on year takes lots of time to display content after selection.

Database Selection on Model Open?

suniljain — Wed, 05 Jan 2011 07:07:53 GMT

The solution given by Miguel is perfect

Database Selection on Model Open?

Wed, 05 Jan 2011 07:20:30 GMT

Thanx this will do, just tested it on a model and it will give me the desired "selection".

Now how do i use it as a trigger when a user open the document with Access Point?

Database Selection on Model Open?

Wed, 05 Jan 2011 07:53:42 GMT

From what i could find is i need to use the OnOpen trigger and write a macro for it to ask the Year correct? Can somebody please help with Miguel's code in the macro?

SV:Re: Database Selection on Model Open?

gandalfgray — Wed, 05 Jan 2011 08:10:14 GMT

Macro code:

sub reload
ActiveDocument.Reload
end sub

SV:Re: Database Selection on Model Open?

Miguel_Angel_Baeyens — Wed, 05 Jan 2011 08:18:23 GMT

Note that some OnOpen and OnPostReload macros may not work when you access the document via client (open in server in Desktop, Plugin or AJAX). I'd better use some headers or passing some variable to the url so the document "know" who's opening the document and what year has to load.

Regards.

SV:Re: SV:Re: Database Selection on Model Open?

gandalfgray — Wed, 05 Jan 2011 08:34:38 GMT


Miguel Angel Baeyens wrote:
Note that some OnOpen and OnPostReload macros may not work when you access the document via client (open in server in Desktop, Plugin or AJAX)... <div></div>

Yes, that's what I noticed too, reloading using ActiveDocument.Reload does not work for me through plugin client.

I do not know if there's some setting to be done which I have not set correctly, this is what I use

and in the client I select "Give Module Script System Access"

but reloading does not work neither triggered OnOpen nor by a button

SV:Re: SV:Re: Database Selection on Model Open?

Miguel_Angel_Baeyens — Wed, 05 Jan 2011 09:03:10 GMT

Hello Göran,

It's not a question of how you have configured your security rather than how the server behaviour is expected. According to the Ref Manual, OnOpen, OnPostReload and OnPostReduceData are not be used in QlikView Server environments, since "they lack meaning or may cause unexpected results".

However, I've read here that some people have got them working.

Hope that helps.

SV:Re: SV:Re: SV:Re: Database Selection on Model Open?

gandalfgray — Wed, 05 Jan 2011 09:33:52 GMT


Miguel Angel Baeyens wrote:
Hello Göran,
It's not a question of how you have configured your security rather than how the server behaviour is expected. According to the Ref Manual, OnOpen, OnPostReload and OnPostReduceData are not be used in QlikView Server environments, since "they lack meaning or may cause unexpected results".
However, I've read here that some people have got them working.
Hope that helps. <div></div>

So the conclusion is that the answer to the original question is: No it is not possible to load conditionally for clients accessing server qvw's.

I would go for Deepaks solution, adding all data to the same application, adding a Year dimension.

Database Selection on Model Open?

Mon, 10 Jan 2011 15:13:18 GMT

I want to do a macro to do this, also executable from a button (run external macro).

How?

My variable is VARYEARSELECTION. On executing the macro ask me to select 2010 or 2011

Database Selection on Model Open?

Miguel_Angel_Baeyens — Mon, 10 Jan 2011 17:16:36 GMT

The macro won't run OnLoad, OnOpen, etc. For the button, I'd suggest to create a VBS for WSH file which is executed before opening the document. Or take the values from a command prompt batch file.

Anyway, since the document is going to reload all data, it doesn't make much sense doing the above. I'd set a first sheet (keeping the rest hidden) in which the user has to select the year, all the tabs are shown based on the conditional.

Count(DISTINCT Year) = 1

In each tab.

Hope this helps