Friday, November 18, 2011

Select Top 4 Values On Open

I was working on a document that contained a lot of data and the performance when the document was opened or when the user moved from tab to tab was a little slow. On my own laptop when I save documents like that I usually make a selection of a small amount of data so that later when I open the document again it will open quickly. I wanted to do something like that for the users -- some kind of selection that would happen automatically when the document is opened, but it had to be a selection of data that would be useful and easy for the users to understand.

The data in the document was organized by a date field named POSTDATE so I decided that I would like the document to automatically select the most recent four POSTDATE values when it was opened. Most users look in the document for the most recent data and anyone who wanted to review older data could easily add older dates to the selection through the multibox.

The method I used is to create a trigger that would fire when the document is opened. The trigger would do a Pareto Select. Now, I'm not saying that this is the best way to accomplish the selection and it certainly is not the only way, but it was easy and didn't involve writing macro code.

Pareto Select is used to select a percentage of values in a field based on ranking of a value or expression. It is commonly used select things like the top 20% best selling products. In this case I wanted to use it to select not a fixed percentage but the four most recent POSTDATE date values. This is what I did:

From the menu choose Settings->Document Properties and choose the Triggers tab.
In the document event triggers click the Add Action button for the OnOpen event.
Click Add Action and in the Add Action dialog choose Action Type=Selection and Action=Pareto Select then click OK.
In the Actions dialog type POSTDATE into both the Field and Selection boxes. POSTDATE is not only the field I want to select but also the expression value I want ranked.
Then, in the Percentage box enter =(4/COUNT(DISTINCT POSTDATE))*100
And click OK. Save the document and the next time it is opened it will automatically select the four most recent POSTDATE values.

A similar method could be used to do automatic selection of any number of top values of any field. Pareto Select does its selection based on the current Possible values. If you wanted to ignore the current selection and select a percentage of all values then you should add an extra action to the trigger to clear the field before making the Pareto Selection.

Testing trigger actions is most easily done by defining the same actions for a button and then you can test the action by clicking the button. When you're done testing, remove the button and make sure the actions are associated with the proper event.

= = = = = = = = = = = = = = = = = = = = = = = = = =
Author's Note 6Dec2011:
This method didn't work the way I wanted when the document already included selections on the POSTDATE field. I couldn't find any good way to deal with that using the available trigger actions. I eventually replaced the Pareto Select action with a macro action that selects the four most recent POSTDATE values. The macro code looks like this:
sub Top_4_POSTDATE
set fd=ActiveDocument.GetField("POSTDATE")
fd.TopSelect "=if(aggr({1} rank(POSTDATE),POSTDATE)<=4,POSTDATE)",4
set fd=nothing
end sub

* * *


Anonymous said...


Where can I find a clear example of how to use isnull in an if statement within the script:
if(IsNull([CustomerName]),[LastName] & " " & [FirstName],[CustomerName]),

The above example I wrote in access, not sure on syntax in QV.

Your help and or suggestions greatly appreciated.


-TB said...

Hi Nick,
Your example looks good as long as you add an alias field name to it. So, in the list of fields under a Load command you would write:
if(IsNull([CustomerName]),[LastName] & " " & [FirstName],[CustomerName]) as CustomerName_2,

-TB said...

Nick, to answer your question about where to find examples-- I find that QlikCommunity is the best place to find examples of most things you can do with QlikView.

Good luck,

Anonymous said...

Yes, it ran as intended, I had to replace teh dbl quote with single quote and add the alias. Thanks for confirming.

Do you have any experience writing the equivalent of an access query, multiple tables(4)with several different joins. I have not been able to find any examples with clear explanantions - Can you help??

I can write the standard QV but I have never created actual queries from Acces and or SQL tables from within QV.

Thanks - Nick

-TB said...

Hi Nick,
I haven't used MS Access in years but I remember that you can show the query code in a SQL view. Then, you could copy and paste the SQL into a QlikView loadscript and load the results of the query (after connecting to the Access database).
If the joins between the tables are based on key fields that are in the joined tables then you could probably just load the individual tables and let QlikView do the associations based on field names. Then, set up your charts and make some selections and you may already be 99% of the way to having the document you want.