topic Re: Dynamic updates to SQL WHERE clause in QlikView

Dynamic updates to SQL WHERE clause

Thu, 06 Feb 2014 19:50:53 GMT

I pretty confident that want I want to do is not the QV Correct way and maybe some of you QV gurus could tell me the preferred method. But, we have a bunch of queries of which this is probably the simplest by far that are being ported from another dashboarding tool over to QV.

The commonly occurring pattern we have is to have drop downs, normally built from a table that when selected update graphs, text fields, tables and gauges. Probably multiple items per drop down change.

I would normally have bound a control to the where clause such as in my example script below.

So the query does what I want and returns the initial value back for 360 days as per the variable initialisation.

So my first question in this forum is how do I bind this to a QV List so that when the value is selected it forces an update.

In QV I have rendered the one value returned by the query in a text box.

Thanks for any help

SET SelectedDays = 365;

Days:

Load * Inline

[Days

7, 30, 60, 90, 120, 365, 720];

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=FinanceDB=WIN-05BI;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WIN-05BIJREC3J4;Use Encryption for Data=False;Tag with column collation when possible=False];

SQL SELECT sum(rspdCaseExtended.TotalCostEntered) AS TotalCostEntered

FROM rspdCaseBase INNER JOIN

rspdCaseExtended ON rspdCaseBase.Id = rspdCaseExtended.Id

WHERE rspdCaseExtended.TotalCostEntered <> 0

and DATEDIFF(d, rspdCaseBase.CreatedTime,getdate()) <= $(SelectedDays)

Re: Dynamic updates to SQL WHERE clause

Anonymous — Thu, 06 Feb 2014 20:02:31 GMT

Kevin

I may well be misunderstanding what you are after, but how about you load the data from all the dates into QlikView.

Then having created suitable QlikView objects you can select the date ~~that you wish displayed.~~

You could maybe put your variable SelectedDays into an input box, so that its value can adjusted by the user and exploit Set Analysis using this variable SelectedDays to just display data from your required date.

Best Regards, Bill

Re: Dynamic updates to SQL WHERE clause

Fri, 07 Feb 2014 00:43:18 GMT

Thanks for the fast reply Bill!

I did expect that someone would come back with your suggestion, I suspect that would be the natural way for QV to want to handle this type of scenario.

We have a lot of rows of data and many, many dates in that data going back many years so didn't really want to consume a ton of memory for one number. I'm not a DB guru so that could well be the wrong way to look at it.

This example is one of many and I'm trying to refactor them all as little as possible. Some of the queries I have have some really complicated joins. I accept I'm trying to be lazy

Re: Dynamic updates to SQL WHERE clause

iktrayanov — Fri, 07 Feb 2014 01:45:30 GMT

I don't think what you want is a good idea but you can try to accomplish this by using an input box with preset values for you variable and then button with external action partial reload. Or instead of button just a trigger on variable set or change.

Re: Dynamic updates to SQL WHERE clause

Fri, 07 Feb 2014 07:46:50 GMT

Thanks Ivan,

Is using a button the only way to get it to refresh, there's no action on a selection of a list? I couldn't find one.

Re: Dynamic updates to SQL WHERE clause

iktrayanov — Fri, 07 Feb 2014 13:47:48 GMT

You can use trigger.

Settings>Document Properties<Triggers> Variable Event Triggers

Re: Dynamic updates to SQL WHERE clause

smoon63 — Sat, 08 Feb 2014 03:05:31 GMT

The way object-level triggers are handled changed in, I believe, v10 or maybe v11. Instead of having a Triggers tab in the Properties for each object, they were moved to the Document Settings level. As Ivan mentioned, you can use triggers that fire by document, field, or variable events. There are also still the sheet level triggers for OnActivateSheet and OnLeaveSheet, but those won't help you here, beyond setting default selections.

In your case you could use a field trigger, but to do things the way you mentioned, a new query will be generated each time a user clicks a new value. To avoid a new query each time a new date range is selected, you could use a variable trigger along with a button to set that up.

However, since QlikView is perfectly capable of handling huge data sets with grace, I would follow Bill's suggestion and load everything in the app, and just handle what is displayed by appropriate selectors. I would recommend using a calendar control with a slider to allow the user to dynamically choose the date range they want to analyze. Or there is always the common set up using selectors for Year, Quarter, Month and Day, or a combination of those techniques.

Hope this helps,

Scott Moon