Conditional Function limitations

[#1]

Hi,

My question is does anyone know further work arounds for the limitations of the conditional logic function provided in Qlikview?

It would be ideal to be able to invoke CASE WHEN logic in an expression.  I have tried the IF() function, as well as the blended PICK(MATCH()) function.  The following describes the limitations I have encountered:

• IF() - I have used nested IFs successfully to create What-If analysis, however I recently encountered a use case for having hundreds of nested IFs.  It appears that Qlikview will not allow a developer to exceed around 100 nested IFs.  It would be great if I could override the default developer specs and force Qlikview to allow more nested IFs, or be able to use CASE WHEN syntax.

• PICK(MATCH()) combo - When I encountered my nested IF limitation I successfully added many lines of code to fill in the PICK(MATCH()), the dilemma here is that I can only match against 1 field at a time.  What I was trying to do was pick the first 8 expressions to match with the 8 values of the Level1 field, and pick the next 12 expressions to match with the 12 values of the Level2 field, and again for the Level3 fields.

In short the nested IF solution has a row limitation, and the PICK(MATCH()) combo is too inflexible.

[#2]

Hi Michael,

would be very helpful to have example of table where you have fields modality and short_modality.

Not sure if it's gonna work but you can try following for pick condition

pick(

if(match(short_modality,'AX','CR','MI','MR','SY','US','XP')>0,match(short_modality,'AX','CR','MI','MR','SY','US','XP'),

        match(modality

        ,'AX AXA'

        ,'AX ECS'

        ,'CR CT'

        ,'CR RO'

        ,'MI PCL'

        ,'MI PET'

        ,'MI SPECT'

        ,'MR'

        ,'SY VIA'

        ,'US'

        ,'XP RF'

        ,'XP SU'

        ,'XP UR'

        ,'XP WH'

        ) + 7,

...

[#3]

As an addition I see relation between your condition and field name.

I believe it is possible to use universal expression which will use variable instead of field name. Variable itself will be filled based on you condition. Any way data sample is needed

[#4 · Accepted solution]

I ended up using VBA scripting to accomplish my design.