IIF

DeepSee MDX Reference

IIF

[Home] [Back] [Next]

InterSystems: The power behind what matters

InterSystems DeepSee > DeepSee MDX Reference > MDX Functions > IIF

Class Reference

Search:

Returns one of two values, depending on the value of a given logical expression.

Returned Type

This function returns a number or a string, depending on the arguments used and the value of the logical expression.

Syntax and Details

IIF(check_expression, expression1, expression2)

expression1 and expression2 are numeric or string expressions. They do not have to be the same type.

DeepSee MDX does not support other types of arguments.

To compare to a null value, use the ISNULL function instead.
check_expression is a logical expression, typically that compares a measure or a property to a constant.

If check_expression is true, the function returns the value given by expression1. Otherwise, it returns the value given by expression2.

Example

For example:

SELECT IIF(MEASURES.[%COUNT]<500, "fewer than 500", "500 or more") ON 0, diagd.MEMBERS ON 1 FROM patients

                                       IIF
1 None                          500 or more
2 asthma                        500 or more
3 CHD                        fewer than 500
4 diabetes                      500 or more
5 osteoporosis               fewer than 500

As a variation, the following query uses %LABEL to apply a suitable caption to the data column:

SELECT %LABEL(IIF(MEASURES.[%COUNT]<500, "fewer than 500", "500 or more"),"Patient Count") ON 0, 
diagd.MEMBERS ON 1 FROM patients

                             Patient Count
1 None                          500 or more
2 asthma                        500 or more
3 CHD                        fewer than 500
4 diabetes                      500 or more
5 osteoporosis               fewer than 500

For another example, the following query uses the value of a property:

SELECT %LABEL(IIF(homed.h1.CURRENTMEMBER.PROPERTIES("Population")>20000,"big","small"),
"Town Size") ON 0, 
homed.city.MEMBERS ON 1 FROM patients
                                 Town Size
1 Cedar Falls                           big
2 Centerville                           big
3 Cypress                             small
4 Elm Heights                           big
5 Juniper                             small
6 Magnolia                            small
7 Pine                                small
8 Redwood                               big
9 Spruce                              small

The following example examines the name of the member and conditionally suppresses display of a measure:

WITH MEMBER MEASURES.iif AS 'IIF(homed.CURRENTMEMBER.PROPERTIES("name")="Pine"," ",
MEASURES.[%COUNT])' SELECT MEASURES.iif ON 0, homed.city.MEMBERS ON 1 FROM patients
 
                                       iif
1 Cedar Falls                         1,120
2 Centerville                         1,106
3 Cypress                             1,139
4 Elm Heights                         1,078
5 Juniper                             1,109
6 Magnolia                            1,122
7 Pine
8 Redwood                             1,128
9 Spruce                              1,108