Qlik Community

Qlik Design Blog

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

Employee
Employee

Which conditional functions do you use?

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

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.

192 Views

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..

192 Views

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

0 Likes
192 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
192 Views
Not applicable

Very helpful. Thanks for sharing.

0 Likes
192 Views
jdvermeire
Contributor

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');

192 Views
jaimeaguilar
Valued Contributor II

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
192 Views
jonascbi
New Contributor III

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


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



192 Views
richard_pearce6
Valued Contributor

Thanks, a good overview of these functions

0 Likes
192 Views
jdvermeire
Contributor

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

0 Likes
192 Views
jonascbi
New Contributor III

If you like that one, you'll probably appreciate this one too...

How to custom sort a dimension in chart without relying on scripted sort orders


Two examples based on sorting of a dimension called Customer:

1. Priority sort matched results, then allow for standard sort options for the rest of the data:

replace(match(Customer,'Shoe Expert','Bobby Socks'),0,10000)

2. Priority sort matched results, then sort the rest of the data in descending order by Expression:

pick(1+match(Customer,'Shoe Expert','Bobby Socks'),

rank(sum(Value)),

0

)

I've uploaded a small example here:


Custom sort order.qvw

Best

Jonas

0 Likes
192 Views
sudeepkm
Valued Contributor III

thanx for the post.

0 Likes
192 Views
thornofcrowns
Valued Contributor II

Thanks, very useful indeed!

0 Likes
192 Views
darrin_pilkingt
Contributor II

All great examples.

I have been using Pick in place of IF whenever I can but can Pick() be used against 2 values? 

something like:

Pick(Match(MyField1 and MyField2, 'Val1', 'Val2', 'Val3')

Thanks

0 Likes
192 Views
jonascbi
New Contributor III

Hi Darrin,

No, match does a string comparison between the resultant text in the first parameter against the text tags in parameter 2, 3, 4... I would satisfy your above logic requirement with something along the lines of:

if(MyField1 = MyField2,

     pick(match(MyField1,'Val1','Val2','Val3')

          'Match1',

          'Match2',

          'Match3'

     )

)

Best

Jonas

0 Likes
192 Views
philip_doyne
Contributor II

Absolutely Jonas I use this a lot it is very clear and substitutes for the lack of a case statement. Do also look up WILDMATCH and MIXMATCH to use as alternatives to MATCH for even greater finesse!

Philip

Philip Doyne

Technical Director, QlickiT Ltd

Mob: 07930 401440

Century Business Centre, Rotherham

South Yorkshire, S63 5DA.

0 Likes
192 Views

pick(match) has some great applications, but I think "if()" is frequently clearer and easier to maintain. Pick/Match requires two parallel arrays that have to be kept in sync, which grows more difficult as the list gets longer.

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

The if() construct keeps the test and result together, so it does not get any more difficult with length. Some people fear the "nested" nature of If(), but you don't need to write it with indentation. It can be written as a simple list style.

if(myField='Val1', 'Ans1'

,if(myField='Val2', 'Ans2'

,if(myField='Val3', 'Ans3'

,'Default'

)))

0 Likes
192 Views
jonascbi
New Contributor III

Hi Rob,

Thanks for elaborating. To mitigate maintenance issues with pick(match()) I tend to apply a bit of commentary and indentation to simplify tracking of pick match, similar to how CASE statements are written in SQL.

Pick(1 + Match( myField,

// Cond1:

'Val1',

// Cond2:

'Val2',

// Cond3:

'Val3'

),

// Default

'Default',

// 1:

'Ans1',

// 2:

'Ans2',

// 3:

'Ans3'

);

I guess it all comes down to personal preference of syntax pattern application as we’re typically not doing anything computationally intensive in the conditionals in scenarios where pick(match(…)) is an option.

Regards

Jonas

Jonas Valleskog

Senior Consultant

Mob: +44 (0)7446 144 572

Tel: +44 (0) 208 100 6515

www.contextbi.com

Context Business Intelligence Ltd

Parkshot House, 5, Kew Road, Richmond, Surrey, United Kingdom, TW9 2PR

192 Views
philip_doyne
Contributor II

Touche! Rob ☺

Its all in the tabbing and layout of your script – another “bee in my bonnet”

I think in my experience badly formatted IF statements have it for maximum UNunderstandability. I appreciate that does not apply to your beautifully formed examples but all to many examples I see are not. Sadly too many developers do not have your (or my) determination to make script understandable at first glance.

I don’t think I would use PICK/MATCH for more than putting a name to 2 or 3 status codes that are unintelligible for instance.

Philip

192 Views
zhouwenjun
New Contributor

Thank you! This is helpful for me.

0 Likes
192 Views
thornofcrowns
Valued Contributor II

This +1!

0 Likes
192 Views
Not applicable

Thank you. It´s very useful

0 Likes
192 Views
zhouwenjun
New Contributor

such as if/left/right/mid/class.I am not familiar to the pick function.I am a newbie,but very interested in QlikView

0 Likes
192 Views
aaroncouran
Contributor III

I prefer to use the substringcount technique but refer to an id of the named field rather than the literal name to make it easier.  The advantage to this is that I only need to change that single character in each condition:

substringcount(concat(dim_id),'z').  You don't even need the separater ('|','/',etc) in the concat function in this use case.  The only deal breaker is that you can't go over the count of unique characters available on your keyboard to identify dimensions.  So if you have hundreds of columns to worry about, maybe not the best solution.

0 Likes
192 Views
santhyamuthu
New Contributor

Kindly explain this.. Will match functions return the corresponding number for the FIRST match in the list of expressions we feed in? For ex, can you tell me what will this return?

match('abc', 'cgf','hjk','abc','abc')

0 Likes
192 Views

Why don't you test that yourself? Create a textbox with as text the expression =match('abc', 'cgf','hjk','abc','abc')

0 Likes
192 Views
datanibbler
Esteemed Contributor

Hi,

thanks! Very helpful! I learned something new - I knew about MATCH and a bit about its variations, I had used the ALT() function only once and I didn't know the CLASS() function at all yet.

I wish there was something like an RSS feed available with all the new blogs - most of them are helpful. (Then I could use calibre to make that RSS feed into an .epub, for instance, and read it at my leisure on the train).

Am I the only one around who thinks that creating a WIKI for all the valuable information here in the blogs, in the community and so on, would be a good idea? That would make it a lot easier to do what admins and mods always tell you to do - to search for solutions before posting questions ...

0 Likes
192 Views
santhyamuthu
New Contributor

Yeah... I had tested that myself and found it returned me the first match. but I wasnt sure about its consistency... Since match would return only one value wanted to be sure enough it picks the first match only and not some match randomly.. There is no mention anywhere in QV forums that match returns the first match

0 Likes
192 Views
adriano_fornoni
New Contributor III

Thanks, I found the post useful

Bye Adriano

0 Likes
192 Views
Not applicable

I am new to Qlikview but learnt a lot from Qlikview 11 for Dev and Cookbook. And both these book are good enough to learn most used functions and techniques. Though Pick(Match()) combination is better than if, I learnt by experimenting in pivot table that when you just use numeric values for results, the totals  and subtotals in pivot table are incorrect in a way similar to max function. For ex.

Pick(Match(Country,'Germany, 'Japan','UK','USA'),1,2,3,4)

Now for each dimension of Country which contains cities, the subtotals is just the number that is returned by the above pick match i.e. 1 for germany, 2 for Japan and so on and also the final total is NULL!!!

So i altered the above expression to return correct results like this-

Pick(Match(Country, 'Germany','Japan','UK','USA'),sum(1),sum(2),sum(3),sum(4))

which caused correct subtotals and totals.

Conclusion: use some expression than some direct values like a number in pick(match())

0 Likes
192 Views