IIF (MDX)
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.
InterSystems 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