Text-based searches are the most frequently used. There are two main options for text-based search: normal and wildcard. The default setting for the search mode is specified in the Presentation tab under Document Properties:

There is a third option in these properties: Use Previous. What this means is that whatever the user has done last will be the default. The user can override the search type that is presented, so this setting remembers whatever they have done.

A wildcard search uses one of the two wildcard characters, in whatever combination we desire, to search for text. The characters are as follows:

Some example searches are shown in the following table:

Wildcards are extremely flexible, but can be very expensive if used to search a lot of data.

If the default search is not a wildcard, you can start typing the * or ? character and QlikView will automatically switch to a wildcard search.

Associative search

The associative search option will search for a value across other fields, not including the field that you are searching in. When you select the value in the associative search, it then selects the values in the field that you are searching in that are associated with the value that you have selected. Ok, that sounds like a bit of a mouthful, so I will give an example. When I click on the search button, say, the Country field, I can see a double chevron (>>) button. Clicking on this button activates the associative search, as follows:

If I type 2009 into this search box now, it doesn't search in Country; it searches every other field except Country. I can see that it has found a value under Year; if I select this and then press Enter, it will select all of the countries that are associated with the value 2009 in Year.

Let's put this functionality in a little more perspective; to achieve the same result without an associative search, we would need to select 2009 in the Year field, select the possible values from the Country field (there is a right-click option to select possible values), and then clear the Year field. It is a pretty cool search function!

Of course, it is not always logical that an associative search should look in every single field. For example, in the preceding screenshot, we see that the field DI.Year is searched. This is a field in a data island table (for more information on data islands, refer to the Data islands section) and so will not be associated. Also, there are many fields in the dataset, for example, keys and numeric values, that should not be searched. It is possible, in the listbox properties, to select those fields that should be included rather than looking at all fields.

In the General tab of the listbox properties, there is a button called More Search Settings that will open a dialog box to allow us to configure this:

Advanced search

The advanced search feature in QlikView is actually incredibly powerful. It allows us to search for values in a field based on the comparison of an expression. It is as if we create a simple straight table with the searched field as the dimension and the expression that we want to calculate, then select those dimensions in the chart that meet whatever criteria we choose.

To open the advanced search dialog, we can right-click on a listbox and select Advanced Search from the menu:

We enter the = sign, which indicates that this is an advanced search, and then the expression that we want to calculate. When we click on the Go button, this expression is evaluated against all the values in the field (in this case, Country), and where it is true, these values are selected.

The really powerful thing about this is that this expression can be as complex as we need it to be. As long as it is valid QlikView syntax, it can be used in an advanced search. All that is needed is that the expression will return a Boolean response: true or false.

All of the text searching options mentioned previously—wildcard, normal, fuzzy, associative, and advanced—also work with numeric fields. Additionally, we can also use a numeric search with numeric fields.

Numeric search

The numeric search option allows us to use combinations of >, <, and = to perform numeric searches. The following are some example combinations:

Example search	Description
>99	This searches for all values that are greater than 99
<99	This searches for all values that are less than 99
>=99	This searches for all values that are greater than or equal to 99
<=99	This searches for all values that are less than or equal to 99
>99<199	This searches for all values that are greater than 99 but less than 199
>=99<=199	This search for all values that are greater than or equal to 99 but less than or equal to 199

When we type the search expression into the numeric listbox, it will react in a way similar to that of a text-based search:

This is quite clever. When we use a search box, we can do any of the standard searches—normal, wildcard, numeric, fuzzy, and advanced—and QlikView will automatically interpret what type of search it is based on what we type. Consider the following scenarios:

We can pass multiple values to a search by enclosing them in parentheses and separating the multiple values using a pipe symbol, for example:

(Germany|China)
(*ge*|*ch*)
(>=2009<=2011|>=2013<=2014)
(>=2009<=2011|*14)

Any valid search syntax will be acceptable within the different values. QlikView will automatically interpret the search based on the rules mentioned.

If we select multiple listboxes either by dragging across them or by clicking on them while holding down the Shift key and then start typing, the subsequent search will be performed across all of the selected listboxes:

We can save a bookmark using the menu options or if there is a bookmark object added to the user interface, we can use that to create it. Either way, the Add Bookmark dialog will appear:

The options for the bookmark are as follows:

The More option from the Bookmarks menu (Ctrl + Shift + B) allows us to manage our bookmarks:

Possibly, the most interesting option here is the Export and Import buttons. These allow us to export bookmarks to an XML file and then import them into different documents later.

We are probably aware that variables can be entered either in the QlikView script via the Variables tab under Document Properties or using the Variable Overview dialog.

When creating a variable in the script, we do this using either the SET or LET keywords. For example, to create the v1 and v2 variables, use:

SET v1=1+1;
LET v2=1+1;

The SET keyword will assign the text on the right-hand side of the equals sign to the variable. The LET keyword will instead evaluate the text on the right-hand side of the equals sign and then assign the result to the variable. If we load this script in the QlikView debugger, we will see the following result:

We can see that v1 has been assigned the text " 1+1", whereas for v2, the text has been evaluated and the value of 2 has been assigned to the variable.

We can achieve a similar result when using the Variables tab under Document Properties or using the Variable Overview dialog, to create a variable. When we add a variable, we can either just enter text in the Definition box, in which case just the text is assigned to the variable, or we can begin the definition with an = sign that causes the expression to be evaluated and the result of the calculation gets assigned to the variable, for example, using the Variable Overview to create v3 and v4, you get the following:

As with v1 and v2, v3 will have the text "1+1" while v4 will evaluate to 2.

There is no difference between using SET in the script and assigning text to a variable in the Variable Overview window. There is, however, a difference between using LET in the script and using = at the start of the variable definition. In the script, the result is calculated once during the script execution and the variable will then have a static value. If we use the = sign at the start of the definition, the variable's value will be recalculated every time there is a new selection made by users.

We will quite often use variables to hold commonly used expressions. That way, if the expression needs to change, then we don't need to hunt down every use of the expression; we can just change the variable.

The best practice here is to define these variables in the script with a SET statement. Quite often, these SET statements are stored in a separate QVS that might be shared with several documents, especially if the expressions in question are for color values that will be used throughout the organization. For example, we can have an external variables file with the following lines:

// Color expressions
SET cCompanyGreen=ARGB(255,20,228,68);
SET cCompanyBlue=ARGB(200,0,32,200);
SET cCompanyAlert=ARGB(255,255,0,0);
SET cCompanyWarning=ARGB(200,255,126,0);

Then, we can load them into our main script using the following:

$(Must_Include=..scriptsvariables.qvs);

If one of the colors needs to change, we can simply update the file and the change will be updated in every document that uses it on the next reload.

We probably have seen variables being used in scripts and expressions and might have come across the concept of Dollar-sign Expansion. This function, which we will delve into in much more detail, allows us to access variable values in a way that is not quite intuitive for those who are used to common programming languages. To add to the confusion, we don't always need to use Dollar-sign Expansion with variables; we can use them sometimes just like other programming languages!

With Dollar-sign Expansion, we will wrap the variable name in parentheses preceded by a dollar sign, for example:

LET vx=$(vy)*2;

When this is processed, what happens is that the value inside the parentheses is evaluated and placed into the expression to replace the dollar sign. Once all the Dollar-sign Expansions have been completed, the whole expression is evaluated with the expanded values. We can think of it as a two-step process, for example, if vy has a value of 2, LET vx=$(vy)*2; becomes LET vx=2*2;.

This is now evaluated and the result, 4, is placed in vx. If we were looking at this script in the debugger window, we would actually see this two-step process in action. The central bar of the debugger will display the line that is about to be executed. If there are Dollar-sign Expansions, then the line that is displayed is with the values already expanded:

In fact, this is one of the situations where we don't have to use Dollar-sign Expansion. If a variable contains just a numeric value, then it is allowable to just call:

LET vx=vy*2;

A Sum of If means that we are performing an aggregation, such as Sum, on the results of an If statement. Consider the following example:

Sum(If(Country='Germany', Sales, 0))

In this case, the Sales value will only be summed if Country is equal to Germany.

On a smaller dataset, you will not see much of an issue with this calculation. However, as the dataset increases in size, we will find that this way of performing calculations is relatively inefficient, not least because the comparison is text-based. Also, we need to consider that if a user selects a set of countries, or any other selection that excludes Germany, then the result will be zero, which might not be what you want to happen.

It isn't a QlikView issue, but just a computer issue; however, any comparison that is done using text values is always going to be more expensive than a comparison using numbers.

One of the ways that we can improve performance of a comparison calculation is to create a numeric flag field in the script. For example, we can do the following in the script:

Load
   ...
   If(Country='Germany',1,0) As Germany_Flag,
   ...

This will create a (quite efficiently stored) field that contains just 1 or 0. We can use this in an expression like the following:

Sum(If(Germany_Flag=1, Sales, 0))

This expression will perform an order of magnitude better than the equivalent text comparison. However, the following calculation will be even better:

Sum(Germany_Flag * Sales)

As there is no comparison happening here, it is a fairly straightforward mathematical calculation for the system to calculate, and it will be performed even faster than the Sum of If.

This type of flag arithmetic is very common, and we will always look to create flags like these in the script to improve the efficiency of calculations in chart expressions. Here are a couple of examples of flags that we will often create in fact or calendar tables:

Load
   ...
   -YearToDate(DateField)        As YTD_Flag,
    -YearToDate(DateField,-1)    As LYTD_Flag,
   ...

In this case, we are using a QlikView function (YearToDate) that returns a Boolean result. In QlikView, Boolean false is always represented by 0. Any non-zero value is Boolean true; however, QlikView functions will always return -1 for true. Hence, the minus sign prefixed to the function will change the -1 result to a 1.

On occasions where we might want some flexibility around what we calculate from what the users select, we might ask those users to change variables, usually using the slider or calendar controls, and then use those variables in the expressions.

For example, if we had two variables called vMinDate and vMaxDate, we can add calendar controls to allow the user to modify them:

We can now add an expression to calculate the sales between those two values in the following manner:

Sum(if(DateID>=$(vMinDate) and DateID<=$(vMaxDate), LineValue, 0))

We can also grab the to-date calculation (for example, to calculate a balance) in the following manner:

Sum(if(DateID<=$(vMaxDate), LineValue, 0))

Using a variable or set of variables can be quite flexible but sometimes we might want to give users even more options for selections, while still keeping those selections separate from the main data model. In these circumstances, we can create a completely separate data model, which has full QlikView selectability, and then derive the values from this data model that should be used in the calculations for the main data model. When we create separate data models like this, the non-main data model is called a data island. For example, we can load a calendar table in the following manner:

// Load the Date Island
Let vMinDate=Floor(MakeDate(2009,1,1));
Let vMaxDate=Floor(MakeDate(2014,12,31));
Let vDiff=vMaxDate-vMinDate+1;

Qualify *;
DI:
Load
   TempDate as DateID,
   Year(TempDate) As Year,
   Month(TempDate) As Month;
Load
   $(vMinDate)+RecNo()-1 As TempDate
AutoGenerate($(vDiff));
Unqualify *;
Set vMinDate="=Min(DI.DateID)";
Set vMaxDate="=Max(DI.DateID)";

The Qualify statement prefixes the name of the table (DI) to each field so that these fields should not be associated to the rest of the data model.

We use the SET statements at the end to add the calculation of minimum and maximum dates to the variables. We can then use those variables in expressions as shown:

Sum(if(DateID>=$(vMinDate) and DateID<=$(vMaxDate), LineValue, 0))

Otherwise, we can use those variables as shown in the following expression:

Sum(if(DateID<=$(vMaxDate), LineValue, 0))

This data island does not need to be just a single table. If it makes sense, it can be a small data model in itself and perhaps two separate calendar tables connected via a link table.

We do have to be careful that a data island does not become a separate data model, with its own facts and dimensions, as this can be against the license agreement when using document licenses.

After having done any basic QlikView training, we will have had some sort of introduction to Set Analysis. This is one of the most powerful features of QlikView and allows us to create some great solutions. Of course, like any powerful feature, there is room for misuse and abuse.

In this segment, we will revisit some of the basics of Set Analysis and will explore more advanced topics later on in this chapter.

Understanding a little about sets is the key to understanding how QlikView works. We already know about the symbol tables and the logical inference engine. A simple Venn Diagram can help us understand how they hang together.

When we load data into a QlikView document and we have no selections made, we will have access to all the data points for the purpose of performing calculations, as you can see:

In mathematical terms, this is our universe. It contains all of the entities that we might want to consider. If we perform a simple Sum calculation across a field, we will get the total value of all the values in that field.

Now, let's consider what happens when we make a selection in QlikView. For example, if we were to select the value 2013 in the Year field, QlikView would immediately apply the logical inference engine to establish all of the values that are still available, as follows:

Now, when we perform the same Sum calculation, we only get a result based on the values contained within the shaded area.

If we were to make a further selection, for example, if we select both China and Germany in Country, then QlikView will further reduce the dataset upon which calculations are performed, as follows:

Now, the calculation of the Sum expression is only performed on the shaded area where the two ellipses overlap.

That, in a nutshell, is how QlikView works. It is beautifully simple and a great way of working with data from a data discovery point of view. However, we often want to think about other considerations. For example, if the user has selected Germany, what might be the value of everything else that isn't Germany? In the preceding model, we no longer have access to the data about "not Germany" because it has been excluded by selection.

Sum({1} LineValue)
Sum({$} LineValue)
Sum({BM01} LineValue)
Sum({[My Bookmark]} LineValue)

Function({Set_ID<Field1=NewSet1, Field2=NewSet2>} FieldValue)

{2009,2010,2011}

Sum({$<Year={2009,2010,2011}>} LineValue)

Sum({$<Year={2011}, Country={'Germany','China'}>} LineValue)

Sum()
Sum({})
Sum({$<>})
Sum({$<Year={}>})
Sum({$<Year={2009,2010,2011}>} LineValue)

Sum({$<Year={$(vThisYear)}>} LineValue)
Sum({$<Year={$(vLastYear)}>} LineValue)