Learning
Community
Open Exchange
Global Masters
InterSystems IRIS Data Platform 2019.4 / Analytics / InterSystems MDX Reference / MDX Functions / %NOT
Previous section   Next section

%NOT

Enables you to exclude a single member of a given level. This function is an InterSystems extension to MDX.

Returned Type

This function returns a member.

Syntax and Details

member_expression.%NOT
Where:
  • member_expression is a member identifier. (Note that you cannot use a general member expression.)
This function enables you to exclude the given member.

Example

Often it is necessary for the WHERE clause to exclude a single member. For example, first consider the following query, which uses EXCEPT:
SELECT aged.[age bucket].MEMBERS ON 1 FROM patients WHERE EXCEPT(aged.[age group].MEMBERS,aged.[age group].[0 to 29])
 
1 0 to 9                                  *
2 10 to 19                                *
3 20 to 29                                *
4 30 to 39                              166
5 40 to 49                              139
6 50 to 59                              106
7 60 to 69                               86
8 70 to 79                               62
9 80+                                    41
You can use the %NOT function to rewrite the previous query as follows:
SELECT aged.[age bucket].MEMBERS ON 1 FROM patients WHERE aged.[age group].[0 to 29].%NOT
 
 1 0 to 9                                  *
2 10 to 19                                *
3 20 to 29                                *
4 30 to 39                              166
5 40 to 49                              139
6 50 to 59                              106
7 60 to 69                               86
8 70 to 79                               62
9 80+                                    41
If you use this function on the column or row axis, you can see that it returns a member:
SELECT aged.[age group].[0 to 29].%NOT ON 1 FROM patients
 
Not 0 to 29                             600
As you can see the name of the member is NOT followed by the name of the excluded member.
The %NOT function provides several advantages:
  • The system does not need to materialize all the members of the level.
  • The negation occurs in an earlier part of the processing for greater efficiency.
  • %NOT returns a single member which can be combined (internally) with other filters to form simple tuple expressions.

See Also

Previous section   Next section