Qlik Community

QlikView Documents

Documents for QlikView related information.

Select case example using SubStringCount() in UI

Luminary
Luminary

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

                                         *Expression1

+

SubStringCount(FieldX,'Case 2')

                                         *Expression2

+

SubStringCount(FieldY,'Case 3')

                                        *Expression3

etc.

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

+

SubStringCount(FieldX,'Case1')*SubStringCount(FieldY,'Case3')

                                        *Expression4

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.

Martin

Attachments
Comments
Not applicable

Thanks for sharing...

Partner
Partner

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()

Fabrice

0 Likes
Luminary
Luminary

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.

=SubStringCount(Product,'ProductA')

                                         *Expression1

+

SubStringCount(SubProduct,'ProductX')

                                         *Expression2

0 Likes
thornofcrowns
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: