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