topic Re: Change the connection script using a macro - newbie question in QlikView

Change the connection script using a macro - newbie question

Wed, 17 Sep 2014 20:54:51 GMT

Sorry for this, but I have been searching the threads for hours with little results. I am a newbie trying to sell the product to my organization, so I am using personal edition to build a demo.

I have a MSSQL data source where there are multiple views. I would like to be able to edit my connection script FROM statement and reload using a series of buttons. My assumption is that multiple macros are necessary, but I am unable to figure out how to change the view from a macro.

The connect script is simple:

ODBC CONNECT TO SERVERNAME;

SQL SELECT *

FROM DATABASE.dbo."VIEW1";

I want a button that changes whatever the view is (VIEW1) to VIEW2 or VIEW3, etc. and then reload the data.

Any help is greatly appreciated!

Re: Change the connection script using a macro - newbie question

Wed, 17 Sep 2014 21:03:49 GMT

I think it can be achieved using a variable, haven't tried though.

Check out this link

Re: Reload Data based on a selected value

Re: Change the connection script using a macro - newbie question

marcus_sommer — Thu, 18 Sep 2014 06:30:54 GMT

You could change the connection and/or databases/views per variables within for / for each loops directly in script. Even further conditions could be applied with integrated if-loops. Here a small example:

for each servername in 'server1', 'server2'

ODBC CONNECT TO $(servername);

for each view in 'view1', 'view2', 'view3'

SQL SELECT *

FROM DATABASE.dbo.$(view);

You could also load your server and views in table and runs then per loop through these table. If possible you should avoid any macros.

- Marcus

Re: Change the connection script using a macro - newbie question

Thu, 18 Sep 2014 12:56:26 GMT

Thank you both so much! I wasn't sure if variables would work in the load script, and my lack of experience with the product is somewhat evident.

As further detail, the reason I did not load the entire dataset is that I was concerned about performance. My table is 1.5M rows and the views are not mutually exclusive meaning that if I load all of the views I could be looking at well over 2M rows. Additionally, this table grows by about 350K each month. I thought it better to let the SQL server do most of the work that rely on my personal edition of QV for testing.

Another note to Marcus - there is an option to set the variable directly through the button as opposed to a macro. I am assuming that this option is much better than a macro from your recommendation.

Many thanks again, and I hope this post is helpful for other newbies.

Re: Change the connection script using a macro - newbie question

marcus_sommer — Thu, 18 Sep 2014 13:45:41 GMT

2M rows are for qv rather small datasets and you need not big machines to handle it. Your database and network is more likely the bottleneck. But it's not very senseful to load every time all data into qv. Here you could use a method called incremental load - have a look here: http://community.qlik.com/docs/DOC-4597

- Marcus

Re: Change the connection script using a macro - newbie question

Thu, 18 Sep 2014 17:24:06 GMT

I'm not quite at that level yet. If/when I get approval for the project and there is an actual server available, then I can look into the more technical side of performance. Still, it is encouraging that the database size will not be a problem.

Thank you again for your feedback and the variable solution did work for now. I am ready to present to the committee and it should go well.