Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

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

9 Comments
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

10,766 Views
hic
Former Employee
Former Employee

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

10,766 Views
Anonymous
Not applicable

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
10,766 Views
thanstad
Creator
Creator

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
10,766 Views
hic
Former Employee
Former Employee

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

10,766 Views
thanstad
Creator
Creator

Thanks a lot.

Tormod Hanstad

0 Likes
10,766 Views
imsushantjain
Partner - Creator
Partner - Creator

@hic @Oleg_Troyansky 

Can we aggregate using AND condition on boolean data type?

Sample Data :

CounterpartyDealConditionCompliance
Counterparty 1Deal 1Condition 1TRUE
Counterparty 1Deal 1Condition 2FALSE
Counterparty 1Deal 1Condition 3TRUE
Counterparty 1Deal 2Condition 1TRUE
Counterparty 1Deal 2Condition 2TRUE
Counterparty 1Deal 2Condition 3TRUE
Counterparty 1Deal 3Condition 1FALSE
Counterparty 1Deal 3Condition 2TRUE
Counterparty 1Deal 3Condition 3TRUE

 

Can i aggregate the Compliance Flag for Each CounterParty and Condition for all Deals to say that its True or False?

eg. For Counterparty 1, Condition 1 is FALSE as Deal 3 has Compliance as False (Basically an AND condition across all flags, TRUE AND TRUE AND FALSE =FALSE).

Input :

CounterpartyDealConditionCompliance
Counterparty 1Deal 1Condition 1TRUE
Counterparty 1Deal 2Condition 1TRUE
Counterparty 1Deal 3Condition 1FALSE

 

Output :

CounterpartyConditionCompliance
Counterparty 1Condition 1FALSE

 

My understanding is basically there is no way to do this is front end aggregation, only way would be write a loop in scripting. 

0 Likes
5,279 Views
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi there,

Actually, it's quite easy to aggregate boolean values with the AND condition. Assuming that the values are truly formated as boolean TRUE and FALSE (with numeric representation of -1 for true and 0 for false), a simple function MAX() will return the aggregated result:

MAX(Compliance) will return -1, or TRUE, if all Compliance values are TRUE. If any of the aggregated values are FALSE, then the result will be 0, or FALSE.

I hope this helps,

Oleg Troyansky

5,245 Views
imsushantjain
Partner - Creator
Partner - Creator

Thank You Oleg, your solution works like a charm 🙂

0 Likes
5,213 Views