Qlik Community

QlikView Documents

Documents for QlikView related information.

Select case example using SubStringCount() in UI


Select case example using SubStringCount() in UI

It is a well known fact, that there is no such thing as a select case statement or equivalent in QlikView which can be used within the UI. To me this makes sense since it is a very heavy calculation which can slow down the application performance and ultimately lead to very inefficient UI development. Best practise is, if one needs to use a select case statement or similar, he should prepone the logic into the script and use the aggregated data as a field in the UI.

However, there are e.g. two cases where a select case statement on UI level is necessary.

1) When you're using data islands to control chart objects. (Conditional show or switch between absolute<>relative)

2) More importantly, when a user is creating an own chart object on the Ajax Client and is trying out different calculations where he wants to implement a select case expression.

Until now, if-statements and more difficult nested if statements are necessary which are complicated (searching the missing brackets...) to write and difficult to understand for someone else.

For a high-level select case I have come up with the following mathematical approach using SubStringCount() e.g.:

=SubStringCount(FieldX,'Case 1')



SubStringCount(FieldX,'Case 2')



SubStringCount(FieldY,'Case 3')



If the dimension or the field selected contains the Case1 string, SubStringCount will evaluate to 1*Expression1, while the remaining Cases will evaluate to 0 and Expressions 2 & 3 will not be considered. An AND Statement can be resembled using e.g. the following




etc. . I also tested the Performance of the Expression with ~45 million rows and compared to a nested if-Statement. The Performance was about just as fast which means it's definitely not a remedy. Case statements should still be used within the script, but for quick calculations or conditional Shows this might be actually useful.

I've attached a sample. Enjoy and Feedback is more than welcome.


Not applicable

Thanks for sharing...


Hi, this is a very interesting approach, i've never thought of this. When I need to use case scenario I tend to use pick() function and create an inline table relating my different cases with a numeric value:

CaseID    Expression

1             Expression1

2             Expression2

3             Expression3

n             ExpressionN

Thanks for sharing, next time I need to implement a case scenario, I'll give it a try to your approach

Not applicable

Why not combine pick() and match() function ? Sth like:


pick(match(Field1, 1, 2, 3), sum(Sales), sum(Unit), sum(Price))

or if it is text:

pick(match(Field1, 'F1', 'F2', 'F3'), sum(Sales), sum(Unit), sum(Price))

that will do: if Field1=1 then Sales, if Field1 = 2 then Unit, if Field1=3 then price

I do not know if it is quicker or not than the SubstringCount()



I agree pick(match()) statement is also a good way of resembling a case statement.

In addition to that, SubStringCount() gives the benefit of modeling  the AND statements (Case1*Case2) and you have the possibility to refer to different fields. In the last case, though, you need to make sure your data is modelled accordingly.






Valued Contributor II

Interesting ideas that I'll look to impliment in my future dashboards, many thanks.

Version history
Revision #:
1 of 1
Last update:
‎2014-02-10 04:56 AM
Updated by: