Optimilization of nested If statement

[KT_Gig96]

Hello everyone,

I would like to optimize my code for better visibility (it works as it should, just need to be shorter).  In code which I post below, each "8" stands for null() which I created in Island table.

As you can see on attached example, code after third line stays the same till the end of the first part of Pick function. Is there a way to use some kind of loop inside pick function? 

Something like:

IF(Vizualization = X,
         $(=IF($(exp_LabelX)= '-', 8,X))),

Working code:

=Pick(

          If(Vizualization = 1,
                      $(=If($(v_Button)=1, 8, 1)),

// Start of repeatable code

IF(Vizualization = 2,
         $(=IF($(exp_Label2)= '-', 8,2))),
IF(Vizualization = 3,
         $(=IF($(exp_Label3)= '-', 8,3)),
IF(Vizualization = 4,
         $(=IF($(exp_Label4)= '-', 8,4)),
IF(Vizualization = 5,
        $(=IF($(exp_Label5)= '-', 8,5)),
IF(Vizualization = 6,
        $(=IF($(exp_Label6)= '-',8,6)),
IF(Vizualization = 7,
      $(=IF($(exp_Label7)= '-', 8,7)))))))))

//  End of repeatable code

,
$(exp_Label1),
$(exp_Label2),
$(exp_Label3),
$(exp_Label4),
$(exp_Label5),
$(exp_Label6),
$(exp_Label7),
null())

Also any suggestion how to resolve it in different approach would be nice to get!

Thanks for every answer

[ajaykakkar93]

there is one way you can do this using the parameterized variable in qlik sense,
it can be used to formulate & minimize redundant code

[Digvijay_Singh]

Not sure but you might want to use coalesce function, it automatically takes 2nd parameter when first value is null.

Coalesce(exp,8)

if exp is null then it returns the 2nd value which is 8.

[KT_Gig96]

Thanks for both answers! Unfortunately I cannot optimize it with any given solution. 

 a) Coalesce - problem with this function is that my exps are not null, they contain "-" symbol. And I still would have to write reduntant code for every expression, am I right? Honestly I did not  know this function so thanks anyway,  I guess it will be helpful in future!  But for this moment I cannot understand how to properly clean up this code with this function 

b) parameterized variable in qlik sense - That was my first thought, beacuse I used it in previous app when I had to use YTD/QTD/MTD variable inside expression. Unfortunately I still did not figure out how to correctly use it in pick function. Do I need to post every possible exp_label as a parameter? If yes, then how can I properly use a pick function? (I tried and I found on community site that there is some problems with typing commas inside parameters)

[marcus_sommer]

I'm not sure if this approach could be optimized in a sensible way. I suggest to consider to change the approach to:

pick(Vizualisation, $(exp1), $(exp2), ...)

or transferring the expression condition-logic to a dimension to be able just to use a single and simple expression like:

sum/count(Value)

Both methods may need more or less efforts within the data-model and to re-design the application.

- Marcus