Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

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.

Boolean.png

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.

Match.png

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

Further reading related to this topic:

Data Types in QlikView

Automatic Number Interpretation

6 Comments
MVP & Luminary
MVP & Luminary

Hi Henric! Thank you for a nice recap of flags.

Ever since the introduction of Set Analysis, it seems that the use of Flags got forgotten, and it really shouldn't be.

Perhaps it's worthy of mentioning that another way of using flags is to multiply the numeric value by the flag value, for example:

sum(Sales * InThisYear)

In this example, InThisYear needs to be either 1 or 0 (Personally, I found it more convenient to work with 1/0 flags than with true/false flags.)

So, to summarize, there are 3 common ways of using the same flag (assuming flag values as 1 or 0):

1.   Sum(if(IsThisYear , Sales))

2.   Sum(IsThisYear * Sales)

3. Sum({$<IsThisYear={1}>} Sales)

The first form is synthetically correct, but shouldn't be used on large data sets because it's too heavy to calculate.

The second form typically performs better than the first, with the caveat that it might get heavy if the Flag is stored in a Dimension table, casing a need in resolving another link before calculating the chart. Plus, the calculation needs to be performed on the whole data set, as opposed to a smaller subset of data.

The third form, despite the awkward syntax, is ultimately the best performing formula - we use QlikView associative logic to pre-filter the data set and the aggregation doesn't involve any IF comparisons. And, if I understand it correctly, the Flag can reside in a Dimension table without causing any performance penalties.

Developers often ask me: "Should we use pre-calculated Flags or Set Analysis?". My answer is "both" - prepare the flag in the script, and then use it in Set Analysis. You'll leverage the best of both solutions. 

Cheers,

Oleg Troyansky

3,047 Views

Oleg

I agree with much of what you say and certainly with the conclusion: Create flags in the script - then you have the choice and can choose the faster method.

But I don't agree with what you say about what is heavy calculate.

There are four ways to use flags:

  1. Sum(if(IsThisYear='True' , Sales))
    The string comparison "IsThisYear='True'" is CPU-expensive. Avoid this construction!
  2. Sum(if(IsThisYear , Sales))
    Using the "IsThisYear" as a boolean is faster than using it in a relational comparison.
  3. Sum(IsThisYear * Sales)
    This construction works fine if the aggregation is a Sum. It does not work with Avg or Count
  4. Sum({$<IsThisYear={1}>} Sales)
    Set analysis is often the fastest way.

I prefer using the flag in set analysis or as a boolean. My experience is that a boolean is often more performant than a numeric comparison and just as performant as a multiplication. So basically I think that if-functions with booleans are not so bad.

I try to avoid the multiplication, since it doesn't work for other aggregation functions.

But I may be wrong - there are many different data models and many different situations, so a multiplicaiton is perhaps sometimes faster. The main point is that you should avoid comparisons in an if statement. Especially string comparisons.

HIC

3,047 Views
juan_escobar
Contributor II

I'm creating flag in every table, so I can sum flags, and I use set analysis between flags. I have found this very useful and quick.

0 Likes
3,047 Views
thanstad
Contributor

This might be too simple question to ask Henric, but is it a similar way to use the SQL "Between" function in QlikVIew. I Assume GE or LE is possible but is there something as MATCH that could be used ?

Tormod Hanstad

0 Likes
3,047 Views

No, QlikView does not have an operator like "BETWEEN".

But it has a prefix "IntervalMatch" that can be put in front of a Load/SELECT to create all combinations between a set of intervals and a set of events. See more here. Very useful if you want such a table. But useless if you want an operator "BETWEEN"...

HIC

3,047 Views
thanstad
Contributor

Thanks a lot.

Tormod Hanstad

0 Likes
3,047 Views