Wednesday, November 19, 2008

Using the Evaluate API in Macros

A common application of QV macros is to make initial selections. These selections may be applied when the document is opened, a sheet is activated or a button is pressed. The selections usually involve some type of dynamic value, such as "today()".

In the examples below, the VBS continuation character "_" is used to indicate the statement continues on the next line. You may type the statement on a single line if wish and omit the "_".

A typical macro to select yesterday's date looks like this:

ActiveDocument.GetField("ShipDate").Select _
Date()-1

It's all VBScript, but the functions used come from two different products. The ActiveDocument...Select is from the QV API, and the Date() function is a VBScript function. To make this work, you need some knowledge of VBS functions. In addition, there may also be a type mismatch -- the VBS function returns a number and the Select expects a string. Fixing that issue requires adding a CStr() or CDbl() function.

What if you want to make a more complex selection. Like the weekday for today? Or a selection that requires knowledge of the data values -- max(ShipDate)?

Wouldn't it be easier if you could use QV Expressions to define the selection expression? You can, with the ActiveDocument.Evaluate API function. Here are some examples.

ActiveDocument.GetField("ShipDate").Select _
ActiveDocument.Evaluate("date(today(1)-1)")

ActiveDocument.GetField("Weekday").Select _
ActiveDocument.Evaluate("weekday(today(1))")

ActiveDocument.GetField("ShipDate").Select _
ActiveDocument.Evaluate("max(ShipDate)")



The Evaluate argument is any expression that can be evaluated by QV. So you can stick with the QV functions you are used to. Those functions are also more likely to produce the correct data type for your selection. And most importantly, you get easy access to the QV data -- ("max(ShipDate)").

You can even use QV search operators such as ">". For example, to select the last 7 days:

ActiveDocument.GetField("ShipDate").Select _
ActiveDocument.Evaluate("'>' & date(today(1)-7)")

I hope you find this tip useful.

-Rob

Tuesday, November 4, 2008

Qliktech Communication

I'm receiving new communications from Qliktech, and I like it.

Jason Long, webmaster of the QlikCommunity site, has engaged community members in a dialog soliciting suggestions for improvements to the Forum. Jason has already implemented a number of requests as well as the very cool Google forum search engine.

I'm now receiving a monthly email from John Trigg, Qlikview Developer Product Manager. The email acknowledges the product enhancement requests I submitted in the prior month. I assume other customers who have submitted enhancement requests are receiving an email as well. This a vast improvement to the old "black box" submission process where I was left wondering if anyone ever read my request. Thanks John!

-Rob