A set can be modified by an additional or a changed selection. Such a modification can be written in the set expression.
The modifier consists of one or several field names, each followed by a selection that should be made on the field, all enclosed by < and >. For example: <Year={2007,+2008},Region={US}>. Field names and field values can be quoted as usual, e.g. <[Sales Region]={’West coast’, ’South America’}>.
A set modifier modifies the selection of the preceding set identifier. If no set identifier is referenced, the current selection state is implicit.
There are several ways to define the selection as described in the following.
Based on another field
A simple case is a selection based on the selected values of another field, for example <OrderDate = DeliveryDate>. This modifier will take the selected values from DeliveryDate and apply those as a selection on OrderDate. If there are many distinct values – more than a couple of hundred – then this operation is CPU intense and should be avoided.
Based on element sets
The most common example of a set expression is one that is based on a list of field values enclosed in curly brackets. The values are separated by commas, for example <Year = {2007, 2008}>. The curly brackets define an element set, where the elements can be either explicit field values or searches of field values.
Unless the listed values contain blanks or special characters, quotes are not needed. The listed values will simply be matched with the field values. This comparison is case insensitive.
If the listed values contain blanks or special characters, or if you want to use wild cards, then you need to enclose the values in quotation marks. Single quotes should be used if the listed values are explicit field values. Then case sensitive matches between the listed values and the individual field values will be made.
Double quotes should be used for searches, i.e. strings that contain wild cards or start with a relational operator or an equals sign. For example, <Ingredient = {"*Garlic*"}> will select all ingredients that contain the string ’Garlic’. Double quotes can be substituted with brackets, for example, <Ingredient = {[*Garlic*]}>. Double quotes can also be substituted with grave accents, for example <Ingredient = {`*Garlic*`}>. Searches are case-insensitive.
In previous versions of QlikView, there was no distinction between single quotes and double quotes and all quoted strings were treated as searches. To maintain backward compatibility, documents created with older versions of QlikView will continue to work as they did in previous versions. Documents created with QlikView November 2017 or later will respect the difference between the two types of quotes.
Forced exclusion
Finally, for fields in and-mode, there is also the possibility of forced exclusion. If you want to force exclusion of specific field values, you will need to use “~” in front of the field name.
Examples and results:
| Examples | Results |
|---|---|
| sum( {1<Region= {USA} >} Sales ) | Returns the sales for the region USA disregarding the current selection |
| sum( {$<Region = >} Sales ) | Returns the sales for the current selection, but with the selection in 'Region' removed |
| sum( {<Region = >} Sales ) |
Returns the same as the example immediately above. When the set to modify is omitted, $ is assumed. Information noteThe syntax in the two previous examples is interpreted as “no selections” in 'Region', that is to say all regions given other selections will be possible. It is not equivalent to the syntax <Region = {}> (or any other text on the right side of the equal sign implicitly resulting in an empty element set) which is interpreted as no region.
|
| sum( {$<Year = {2000}, Region = {US, SE, DE, UK, FR}>} Sales ) | Returns the sales for current selection, but with new selections both in 'Year' and in 'Region'. |
| sum( {$<~Ingredient = {“*garlic*”}>} Sales ) |
The field Ingredient is in AND mode. Returns the sales for current selection, but with a forced exclusion of all ingredients containing the string 'garlic'. |
| sum( {$<Year = {“2*”}>} Sales ) | Returns the sales for the current selection, but with all years beginning with the digit “2”, i.e. most likely year 2000 and onwards, selected in the field 'Year'. |
| sum( {$<Year = {“2*”,”198*”}>} Sales ) | As above, but now also the 1980:s are included in the selection. |
| sum( {$<Year = {“>1978<2004”}>} Sales ) | Returns the sales for the current selections, but with a numeric search used to scope the range of years to sum the sales across. |
If you want to force the exclusion of specific field values in objects such as text objects, you have to modify the script syntax. For example, if your script statement is the following:
=count({<ANDActor=>}DISTINCT Title)
Modify it as follows:
=count({<~ANDActor=, ANDActor=>} DISTINCT Title)
