On Boolean Fields and Functions - Qlik Community

hic · ‎2012-12-07

QlikView does not have any data types. Instead there is the dual format.

But it is still relevant to talk about data types, because QlikView functions and operators always return specific data types, albeit in the dual form. Further, QlikView interprets dual parameters and operands differently depending on the expected data type.

And how does this work for Boolean functions?

All Boolean functions and operations, e.g. IsNull(), True() and comparisons such as Date=Today() return 0 for FALSE and -1 for TRUE. Why minus one? Because it is equivalent to setting all the bits in the byte to 1, which is how TRUE is represented in most software systems (as opposed to FALSE, where all bits are set to 0).

Further, in situations where QlikView expects a Boolean, e.g. in the first parameter of the if() function or in a Where-clause, QlikView will interpret 0 as FALSE, and all other numbers as TRUE.

This means that a number of functions can be used as either Boolean or numeric functions, e.g., Index(), Match(), Substringcount(), and FieldIndex(). For instance, the Match function compares an expression with a list of values and returns the position of the match. But when the Match() function is used in a Boolean position, it will be interpreted as TRUE or FALSE.

So when you use Match() in a where clause, you will have a condition that is very similar to the SQL “IN” operator. Further, with the WildMatch() function you can use wildcards, just like in the SQL “LIKE” operator:

Load … Where Match(Char, 'A','B') or WildMatch(Name,'*son')

SQL SELECT … WHERE Char IN ('A','B') OR Name LIKE '%son'

But the real power of Booleans in QlikView becomes obvious when you define flags in the dimensional tables. For instance, you can easily define a field in the master calendar that tells you if the date belongs to this year:

If(Year(Date)=Year(Today()), True(), False()) as IsThisYear

Then you can use this flag in expressions showing the numbers for this year only:

Sum(if(IsThisYear , Sales))

Or the same expression with Set Analysis:

Sum({$<IsThisYear={'-1'}>} Sales)

Similarly, you can create flags for almost anything: IsLastYear, IsThisYearToDate, IsThisMonth, IsShipped, IsFirstClassCustomer, etc.

One nice thing about flags is that they are stored in the dimensional tables, which are relatively small tables. Hence, the flags don’t use much memory space. Secondly, QlikView evaluates expressions with flags relatively fast. A third advantage is that it is an efficient way for a script developer to define concepts that may be fairly complex, in a way that the business user easily understands.

Conclusions:

Use the Match and Index functions as Boolean functions in Where-clauses and If-functions.
Create flags in the dimensional tables and use these as Booleans in expressions.

HIC

See what other QlikView developers think about flags: iQlik, QlikView Addict and QlikView Notes