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.