Qlik Community

Ask a Question

Qlik Design Blog

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

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
Jennell_McIntire
Employee
Employee

What does Class, Alt, Pick, If, Match, Mixmatch and Wildmatch all have in common?  They are all conditional functions that can be used in the QlikView script or in the user interface.  Personally, I have only used two of them – Class and If which is why I thought it would be interesting to learn a little more about them and their capabilities.  These functions return a value based on a comparison or a condition.  Let’s take a quick look at each of these.

Class

Class allows you to create buckets/groupings of data.  For example, assume you have a data model with order data and there is a field with the number of days an order is late.  Using class, the number of days can be grouped based on an interval we select.  The expression below will create buckets with 30 day intervals:

Class script.png

The buckets will look like the image below.

Class buckets.png

Alt

The Alt function will return the first parameter that has a valid number representation (including dates).  So in the script below, I can use Alt to check the format of the dates in the TempDate field.  If the format matches one of the parameters, then it is returned otherwise the last parameter in the Alt function is returned.  The order of the parameters indicates the priority order so that can be used to determine how the dates should be interpreted.  For example, should 7/4/2014 be interpreted as the 4th of July in the US or the 7th of April in the UK?

Alt script.png

Here are the results:

Alt table.png

The TempDate is the original date and the ValidatedDate is the value returned by the Alt function.

Pick

The Pick function will return the expression/value corresponding to the expression that matches the first parameter.  For example, Pick(2, ‘A’, ‘B’, ‘C”) will return B because B is the second expression and Pick(Number, Sum(1+1), Sum(2+2), Sum(3+3), Sum(4+4), Sum(5+5)) will return 6 if Number = 3.  This function is excellent when you want to generate a random field value, e.g.

Pick script.png


If

Everyone has used an If statement at some point whether in QlikView or some other programming language.

If(condition, then , else)

The If statement check a condition (the first parameter) and if the condition is true, it returns the “then” parameter otherwise it returns the “else” parameter.

For example, if(1+1=2, ‘Woo hoo, I can add’, ‘Need more practice’).

Match, Mixmatch & Wildmatch

These functions are very similar in that they all perform a comparison.

  • The Match function does a case sensitive comparison between the first parameter and the expressions

          match( str, expr1 [ , expr2,...exprN ] )

          Match(X, ‘Jan’, ‘Feb’, ‘Mar’, ‘Apr’)

          If X=Jan, 1 is returned

          If X=’Feb’, 2 is returned

  • The Mixmatch function does a case insensitive comparison between the first parameter and the expressions

          mixmatch( str, expr1 [ , expr2,...exprN ] )

          Mixmatch(X, ‘Jan’, ‘Feb’, ‘Mar’, ‘Apr’)

          If X=Jan, 1 is returned

          If X=’feb’, 2 is returned

  • The Wildmatch function also does a case insensitive comparison between the first parameter and the expressions and allows the use of wildcards

          wildmatch( str, expr1 [ , expr2,...exprN ] )

          Wildmatch(X, ‘Ja*’, ‘F?b’, ‘mar’, ‘Apr’)

          If X=jan, 1 is returned

          If X=Feb, 2 is returned

          If X=Mar, 3 is returned

Now that I know a little more about some of the other conditional functions available in QlikView maybe I will find the need to use them in my apps when I need to compare data or check a condition.  There are numerous ways these functions can be used besides what I discussed here so I am sure they will be useful in many of my future apps.  For more details and an example QVW of these conditional functions in action, check out my technical brief.

Thanks,

Jennell

31 Comments
Gysbert_Wassenaar

Great overview of these functions!

I also use substringcount quite a bit in conditional expressions to show/hide chart objects or dimensions and expressions in charts. For example Substringcount(concat(distinct '^' & MyDimension & '^'), '^ValueA^) will evaluate as true if ValueA is a possible value in MyDimension and false if it's not. So it can be used as a conditional expression to show/hide things depending on what values the user selected. The ^ characters are to make sure of an exact match.

4,370 Views
rwunderlich
Luminary Alumni
Luminary Alumni

I find the SubStringCount technique to be a bit awkward because of the need to add those bracket characters. I've switched to using

max(match(MyDimension, 'ValueA'))

It also allows for case insensitive comparison using mixmatch..

4,370 Views
Gysbert_Wassenaar

Very nice! Definitely an improvement. I like it. Thanks for sharing.

0 Likes
4,370 Views
Not applicable

Rob Wunderlich escribió:

I find the SubStringCount technique to be a bit awkward because of the need to add those bracket characters. I've switched to using

max(match(MyDimension, 'ValueA'))

It also allows for case insensitive comparison using mixmatch..

The result obtained by this method is the same that we will obtain with an If function 


if(my_dimension = 'value',1,0) 


I´ve been using the If method, but it seems that Your method is better in memory usage 

thanks for sharing

0 Likes
4,370 Views
Not applicable

Very helpful. Thanks for sharing.

0 Likes
4,370 Views
jdvermeire
Creator
Creator

I frequently use a combination of Pick() with either Match() or WildMatch() as a way to simulate a "switch" statement:

Pick( Match( myField, 'Val1', 'Val2', 'Val3'), 'Ans1', 'Ans2', 'Ans3');

I find this more manageable than several layers of nested If()s.  Using WildMatch() has the added benefit of providing a default value:

Pick( WildMatch( myField, 'Val1', 'Val2', 'Val3', '*'), 'Ans1', 'Ans2', 'Ans3', 'Default');

4,370 Views
jaimeaguilar
Partner
Partner

I use this Pick/match technique a little bit. I think is quite more efficient in terms of memory usage than nested ifs. a very useful "Switch" scenario would be to have an expression that varies depending on the dimension value.

0 Likes
3,517 Views
JonasValleskog
Partner
Partner

Elaborating on pick(match()) I use this tweak of for setting a default:


Pick(1 + Match( myField, 'Val1', 'Val2', 'Val3'), 'Default', 'Ans1', 'Ans2', 'Ans3');



3,517 Views
richard_pearce6
Luminary Alumni
Luminary Alumni

Thanks, a good overview of these functions

0 Likes
3,517 Views
jdvermeire
Creator
Creator

Jonas Valleskog - So simple, yet so effective.  One of those "why didn't I think of that" moments.

0 Likes
3,517 Views