SET Analysis in Qlikview Part2

Search string
Sometimes, we want to get all MANUFACTURERs with a specific name or which contain specific letters in
their name:
Syntax: {<Dimension = {"*search string*"}>}
· * = 0 to several characters (P1* will return P1, P10, P11 …)
· ? = 1 character (P1? will return P10, P11 … but not P1)
We can do several search strings in the same set:
Syntaxe : {<Dimension = {"*search1*", "fixed text", " ?earch2*"}>
Ex : {<MANUFACTURER_LDESC = {"ENT*", "?LIS*"}, CATEGORY_LDESC={"A?? "} >}
It is why we get all members (except NULL value because the NULL values do not have any character) of a
dimension with the star: <Dimension = {"*"}> or as explained earlier by placing nothing at the right of the equal sign <Dimension =>. The two following sets are identical if you do not have any NULL value in your field. If you have any NULL value in your field and want to select it also, choose <Dimension=>. In the
database used as example, there is no NULL dimension value:
Ex: ALL Manufacturers, only ONE category
{<MANUFACTURER_LDESC = {"*"}, CATEGORY_LDESC={"ACC"} >}
{<MANUFACTURER_LDESC =, CATEGORY_LDESC={"ACC"} >}
Ex: All MANUFACTURERs except those whose name begins with ENT
{<MANUFACTURER_LDESC = {"*"} - {"ENT*"}>}
{<MANUFACTURER_LDESC = - {"ENT*"}>}
Ex: All MANUFACTURERs of the selection + those whose name begins with ENT
{<MANUFACTURER_LDESC += {"ENT*"}>}
Ex: All MANUFACTURERs of the selection except those whose name begins with ENT
{<MANUFACTURER_LDESC -= {"ENT*"}>}

Take care: {<MANUFACTURER_LDESC -= {"ENT*"}>} and {<MANUFACTURER_LDESC = -
{"ENT*"}>} are very different. The first set removes from the current selection all MANUFACTURERs
whose name begins with ENT, while the second set selects all MANUFACTURERs except those who begin
ENT : the {"*"} is by default.
Ex: All MANUFACTURERs, Alls categories whatever the selections
{<MANUFACTURER_LDESC = {"*"}, CATEGORY_LDESC={"*"} >}
Ex: All years (because it is numeric, no quote), all categories
{<Year = {*}, CATEGORY_LDESC={"*"} >}
%Categorie: sum([Sales])/sum({$ <CATEGORY_LDESC={"*"}>} [Sales])
%Fabricant : sum([Sales])/sum(TOTAL [Sales])
Fabricant = Manufacturer. Fabricant is a dimension of the tableau. Category is not a
dimension: it has been set through a ListBox.
QlikView accepts the single or double quotes, the square brackets also for the search string:
Ex:
{<MANUFACTURER_LDESC = {"E*"}, CATEGORY_LDESC={"A??”} >}
{<MANUFACTURER_LDESC = {‘E*’}, CATEGORY_LDESC={‘A??’} >}
{<MANUFACTURER_LDESC = {[E*]}, CATEGORY_LDESC={[A??]} >}
4.4 Using a boundary for an integer dimension
Most of the time, Years are numeric. That is also the case for many keys because QlikView (as SQL) is faster when joining numeric keys than string keys.
The syntax is quite strange because you need to insert the code between double quotes:
{<TIME_KEY = {"<12”} >}: All the TIME_KEY members less than 12
{<TIME_KEY = {">=12 <=18”}>}: key members between 12 and 18, both included
Ex: keys 4, 5, 6 and those between 12 and 20 (20 is excluded)
{<TIME_KEY = {4, 5, 6} + {">=12 <20”}>}

Using a variable
4.5.1 Using a variable storing one or several members
Syntax: <Dimension = {$(NameVariable)} >
Or <Dimension = {$(=NameVariable)} >
(with or without the = sign in front of the variable)
Please note that $(=NameVariable) will interpret the variable and therefore QlikView rewrites the
command before executing it.
The content of the variable may contain several members, as if they were in the real syntax. Enclose them
between quotes or bracket according to their name.
Ex :
<MANUFACTURER_LDESC = {$(vChoiceMANUFACTURER)}, CATEGORY_LDESC={$(=ChoiceCategory)}>
There is another $ sign in the set analysis that means the current selection
$(NameVariable): it can be enclosed between double quotes or not like in the example above. If there is no quote, you may add some in the text of the variable because the member names contain spaces.
4.5.2 Using a variable storing a search string
A normal set: {<MANUFACTURER_LDESC = {"E*"}, CATEGORY_LDESC={"A??”} >}
The variable vChoiceProduit stores a search string and also the double quotes "E*":
{<MANUFACTURER_LDESC = {$(vChoixProduit)}, CATEGORY_LDESC={[A??]} >}

Using a variable storing integer members
With integer keys, you need to enclose the variable name between double quotes:
{<TIME_KEY = {"<$(vFirstPeriod)"}>}
Or {<TIME_KEY = {"<$(=vFirstPeriod)"}>}
The periods between two dates:
{<TIME_KEY = {">$(vFirstPeriod) <$(=vLastPeriod)"} >}
4.5.4 Using a variable storing a dimension
We have already seen that we can use variables with the $ sign. This variable may contain members but
also a dimension:
Ex: sum( {$ <$(vDim)={"*"} >} [Volume Sales])
Because the dimension may contain spaces or specific characters, it would be safer to use the square
brackets (remember that QV rewrites the syntax, so once your dimension name with a space replace the
variable, you would add brackets): sum( {$ <[$(vDim)]={"*"} >} [Volume Sales])
4.5.5 Using a variable storing the whole set
We can store members or dimension into a variable. Why not put the whole syntax?
Ex: sum({$ <$(vSet) >} [Volume Sales])
Ex: sum({$ <$(vSet), CATEGORY_LDESC= "ACC" >} [Ventes Volume])
As you see, we can store the complete syntax or just part of it!
The vSet variable must contain a valid syntax except <> like :
- MANUFACTURER_LDESC ={"*"}, TIME_SDESC={"P 01/13"}
- Year={$(=max({1} Year))}, TIME_LDESC=
- …