If statement format

[Anonymous]

Hi, I am building out a chart with a traffic light system. I am creating the following dimension in script. (They will ultimately be pulled in via expressions in the chart with SALES_PERSON as the dimension )

What I want to know is whether my if statement format is correct and does there always have to be an else statement ?

I have the following :

If (REGION like 'America*' and STATUS like 'Approved*' AND SURVEY_QUESTION='No','qmem://<bundled>/BuiltIn/led_y.png' ), //////////YELLOW TRAFFIC LIGHT///////////

If (REGION like '*Italy*' and STATUS like '*Pending*' AND LEN(SURVEY_QUESTION)<1,'qmem://<bundled>/BuiltIn/led_g.png' ), //////////GREEN TRAFFIC LIGHT///////////

If (REGION like '*Italy*' and STATUS like '*Pending*' AND SURVEY_QUESTION='Yes,'qmem://<bundled>/BuiltIn/led_r.png' ), //////////RED TRAFFIC LIGHT///////////

        as REGION_PERFORMANCE,

Is this synatx in a correct format ?

Thanks in advance

[Anil_Babu_Samineni]

Seems ok to me, May be this

If (REGION like 'America*' and STATUS like 'Approved*' AND SURVEY_QUESTION='No','qmem://<bundled>/BuiltIn/led_y.png',

If (REGION like '*Italy*' and STATUS like '*Pending*' AND LEN(SURVEY_QUESTION)<1,'qmem://<bundled>/BuiltIn/led_g.png',

If (REGION like '*Italy*' and STATUS like '*Pending*' AND SURVEY_QUESTION='Yes,'qmem://<bundled>/BuiltIn/led_r.png') as REGION_PERFORMANCE,

[Peter_Cammaert · Accepted solution]

Your expression is not ok, because you seem to list IF() functions separated by comma's. However, a comma is a parameter separator, nothing else.

This is what you have:

IF (...), IF (...), IF (...), AS REGION_PERFORMANCE

which is an illegal column value expression. Imagine that sequential evaluation would be allowed in QV expressions, and all expressions return true, then what value should be stored in the field?

If you want the second IF() only evaluated if the first IF() condition fails, you need to specify 3 else expressions, including a simple default value to be used in case none of the IF() functions return true. Like in:

IF (Condition1, 'Image1', IF (Condition2, 'Image2', IF (Condition3, 'Image3', 'Image4'))) AS ...

Image4 is the default value, when all other attempts fail. Without a default value, NULL() will be stored.

Or formatted differently:

IF (Condition1, 'Image1',

                 IF (Condition2, 'Image2',

                                  IF (Condition3, 'Image3',

                                                   'Image4'

                                     )

                    )

   ) AS REGION_PERFORMANCE

And indeed, this IF is a function - to be used in expressions, not a (control) statement.

Best,

Peter

[Anonymous]

Hi - This makes sense. I understand there needs to be a default value ie image 4 but what if this also needs to be conditional ?

So we correct the original statement by adding in image 4 as in comments below :

If (REGION like 'America*' and STATUS like 'Approved*' AND SURVEY_QUESTION='No','qmem://<bundled>/BuiltIn/led_y.png' ), //////////YELLOW TRAFFIC LIGHT///////////

If (REGION like '*Italy*' and STATUS like '*Pending*' AND LEN(SURVEY_QUESTION)<1,'qmem://<bundled>/BuiltIn/led_g.png' ), //////////GREEN TRAFFIC LIGHT///////////

If (REGION like '*Italy*' and STATUS like '*Pending*' AND SURVEY_QUESTION='Yes,'qmem://<bundled>/BuiltIn/led_r.png' , //////////RED TRAFFIC LIGHT///////////

                      'qmem://<bundled>/BuiltIn/led_b.png')))   ///  default value image 4

        as REGION_PERFORMANCE,

but what if my default value is only to return everything where REGION = Italy as opposed to just 'else return everything' ?

[Peter_Cammaert]

Add another IF() function call that simply checks whether the region is Italy and ignores all status values.

The default value is only needed if you want to cover all possibilities. If you want to use the NULL value to indicate an unknown state, then omit the default ELSE value. Or add an ELSE clause that displays a blank picture.

Best,

Peter