Keeping it positive

I had a request that when the result of a particular expression was negative, then it should just display as zero (it was a measure of how far out of compliance the result was - a negative result meant that they were in compliance so zero was appropriate).

So I could do a simple:

if(myExpression > 0, myExpression, 0)

That is OK if the expression is simple, but if the expression is more complex then it becomes unwieldy and long and hence difficult to maintain.

A much more elegant way is to use RangeMax like this:

RangeMax(0, myExpression)

Very simple, if my expression dips into the negative then the max value in the range becomes 0. Exactly what I want to achieve and much easier to maintain.

Lies, damned lies...and then there is statistics

I spotted this in a Microstrategy publication which showed the "TCO" for Microstrategy over other operators:



This is a fairly typical use of a flawed statistic to prove a point.

You see, if you have a 10 user QlikView system, you have 1 IT person looking after it - the same guy that is looking after Windows, Email, buying coffee, etc. The same guy can probably look after QlikView as the company grows. So that is a 1 IT person for 10. Push that out to 1000 users and it become a "statistic" of 100 IT people per 1000.

Given that, until recently, QlikView has been playing more in the SME sector, then these numbers are going to be more than less typical. Therefore it probably says something that the "statistic" is only 21.9/1000.

At the other end, of course Microstrategy are best because of only 4.9 IT people per 1000. There is no real qualatitive data behind this. Microstrategy play in the Enterprise sector where they will regularly sell large CAL deals as well as open ended deals - we have no idea how many, exactly, users there are.

Its probably good to know that while your 21.9 QlikView IT people are busy looking after the Email server and making the coffee, your 4.9 Microstrategy guys are busy doing Microstrategy and nothing else.

Using Alerts in QlikView

Alerts are quite easy to set up. With a few tricks, they can become quite powerful.

Let me start with a very simple data set:

CountrySales:
LOAD * INLINE [
Country, Sales
England, 400
Ireland, 300
Scotland, 200
Wales, 100
];

Now, I want to create an Alert when there are any countries that have Sales > 200.

To create an alert, I need to be able to create an expression that will return a true value. I could use a simple aggr expression like:

=Sum(Aggr(If(Sum(Sales)>1, 1), Country)) > 0

This will work. However, it means that we are going to be limited to what information we can include in the Alert message. We can include the number of Countries, but we couldn't, for example, say which ones they were.

A more powerful way is to combine Advanced Search, Bookmarks and Alerts.

If I create an Advanced Search against the Country field such as:

=Sum(Sales)>200

Then I save this search as a Bookmark. Now, I can create an Alert with this Bookmark used where the Alert is simply:

=Count(Distinct Country) > 0

And the Alert message could be something like:

='Number of countries exceeding 200 = ' & Count(Distinct Country) & chr(13) &
'Countries: ' & Concat(Country, ', ')

Of course, I can bring any other information into this message that is related to the countries that match the Bookmark.

Now, if I set-up the "Mail" options under User Preferences, I can have this document email an alert to someone.

If I turn on the alert to use "Batch Mode", this can happen on the server when the document is reloaded.

AND Mode in list boxes

By default, multiple selections in list boxes are "OR" mode. For most applications this is exactly what we need.

However, there are situations, such as that requested by Wolfgang Praschnig on QlikCommunity this week:
http://community.qlikview.com/forums/p/22224/84898.aspx.

Wolfgang wants to be able to see customers who have bought one product AND bought another product. The default action of QlikView would be to show customers who had bought one product or the other.

To enable "AND mode" in a list box, there are a couple of rules that we need to follow:

1. The field in the list box must be contained in a table that has only 2 fields.

2. The other field in the table is the association (or ID) field. This field is only contained in this table.

3. The table must have been loaded with the "DISTINCT" keyword.

This can sometimes be tricky to achieve, so hopefully an example will help demonstrate.

Lets say that we have 3 tables:

Orders
OrderID, CustomerID, OrderDate

OrderDetails
OrderID, LineNo, ProductID, Quantity, Price

Product
ProductID, ProductName

What we want to do is connect the product name to the Orders table in a way that we can employ AND mode.

This means that we will need to create a table that has the OrderID (to associate to Orders) and a ProductName field (which we will call "ProductNameAND") that we can put in our List Box with AND mode on.

So, first we need to create a table with the OrderID and ProductID from the OrderDetails table:

ProductAND:
LOAD DISTINCT
OrderID, ProductID As PID
RESIDENT
OrderDetails;

Note that I have aliased the ProductID field as PID - this is because I am going to drop it in a minute. Also note the DISTINCT keyword.

Now, bring in the Product name:

LEFT JOIN (ProductAND)
LOAD DISTINCT
ProductID As PID, ProductName As ProductNameAND
RESIDENT
Product;

Finally, drop the PID field so we are left with only the 2 fields as required.

DROP FIELD PID;

Now, we can add the ProductNameAND field in a List Box and the AND mode will be available.

Interesting feature here. When you click on an item in an AND mode box, an "&" will appear to the left of it. Pressing CTRL and clicking on additional "possible" (in white) items will add further "&"s beside those items. However, if you click on an "excluded" item or you click and hold the mouse down for a second on "possible" items, it turns to a "!" instead - i.e. "NOT". This can be really powerful for queries.

SR2 released and looking good

Well SR2 for QlikView 9 was released last week.

So far, so good. Looks like all my reported bugs have been fixed.

My opinion is that this Service Release is really what SR1 should have been if it hadn't been rushed forward to get some of the bad bugs from the initial release fixed.

With all of the great things that are in v9, we can now get on with the business of implementing it freely without having to worry about bugs that just shouldn't have been there.

Hurry over to QlikCommunity - http://community.qlikview.com - to get your copy.

Retrieving Database Values from within a document

There may be many reasons why you might want to get a value from outside of QlikView at any time, without having to reload your document (even partially).

The obvious method is to use macros to retrieve the data using ADO calls.

However, there is a function in QlikView that can do this - but only to retrieve one value (e.g. a record count). The function is called SqlValue.

The function takes 3 parameters:

- Connection
- Query
- Connection Type

Connection Type is only one of 'OLEDB' or 'ODBC'. If you leave it out, ODBC is assumed.

The Query is a string that should (although not absolutely required) return one row and one value. If it returns more, only the first value in the first row is returned.

The Connection is straightforward if using ODBC because you just use the predefined ODBC connection name. For OLEDB, you need to pass an OLEDB connection string. Now, this can be messy (as they can get quite long and include single quotes which can throw you a bit. For me, the easiest way is to set up a variable with the Connection string in it (perhaps it could be stored in the registry and retrieved using GetRegistryString!)

Once you have it, then you just call like this:

=Num(SqlValue(MyDatasource, 'SELECT Distinct Count(*) As MyCount From Orders', 'OLEDB'))

Note that I have used the Num function here to cast the return value as a numeric. The SqlValue function returns a dual so, if you want to display it, you need to cast as numeric or it will display as blank (no Text value in the Dual).

The query that you pass can be as complex as the target database supports and can use values from your QlikView document:

=Num(SqlValue(MyDatasource, 'SELECT Distinct Count(*) As MyCount From Orders Where CustomerID in (' & Concat(Distinct CustomerID, ',') & ')', 'OLEDB'))

Obviously, and this is flagged in the help file, if you have queries running in your document, they can slow things down while waiting for the database query to return.

Removing Fields with a Wildcard

One of my clients asked an interesting question. They will have a standard set of QVDs to load data from that may be used in various documents. In each document, they will also have a mapping table to mass rename the fields that they need for that particular document.

Now, with their dataset, they will be left with a load of fields that start with the word "Custom_" - sometimes some of these fields will be aliased, sometimes they will not be used. If they are not used, then they will want to drop the fields from the document (to keep things nice).

Unfortunately, QlikView does not accept a command like

DROP FIELDS "Custom*";

So, we can use a couple of functions that are available in the script to do this for us. Here is a script sample:

MyTable:
LOAD * INLINE [
F1, F2, F3, F4
1, 2, 3, 4
];

Rename Field F1 to NewField;
Rename Field F3 to NewField2;

Let i = 1;

Do While i <= NoOfFields('MyTable')

Trace Getting Field $(i) From MyTable;

Let FieldName = FieldName($(i), 'MyTable');

Trace FieldName = $(FieldName);

Let Command = If('$(FieldName)' Like 'F*', 'Drop Field $(FieldName);', '');

Trace Command = $(Command);

$(Command)

Let i = $(i) + If('$(Command)' = '', 1, 0);

Loop

So, I load 4 fields, rename 2 of them, then loop through all the fields looking for a match ('F*') for the original names and create a command to execute in the script. (This is also a good example of using a dynamically created command in a script!)

The reload window should look like this:

MyTable << INL47EA 1 lines fetched
Getting Field 1 From MyTable
FieldName = NewField
Command =
Getting Field 2 From MyTable
FieldName = F2
Command = Drop Field F2
Getting Field 2 From MyTable
FieldName = NewField2
Command =
Getting Field 3 From MyTable
FieldName = F4
Command = Drop Field F4

Note that you need to use a While statement here not a For loop. This is because once you drop a field, say Field #2, the next field becomes that field number - Field #3 becomes Field #2 - so you will end up skipping fields. The While statement prevents this.