Learning
Community
Open Exchange
Global Masters
InterSystems IRIS Data Platform 2019.3 / Analytics / InterSystems MDX Reference / MDX Functions / INTERSECT
Previous section   Next section

INTERSECT

Returns a set that consists of the elements that occur in both of the two given sets, optionally eliminating duplicates in that set.

Returned Type

This function returns a set.

Syntax and Details

INTERSECT(set_expression1, set_expression2, ALL)
Or:
INTERSECT(set_expression1, set_expression2)
  • set_expression1 and set_expression2 are expressions that evaluate to sets.
  • The optional keyword ALL, if included, specifies that all duplicates in the second set should be retained. By default, if the returned set includes any duplicate elements, only the first of those is included.
    This keyword does not affect duplicates in the first set.

Example

Consider the following query which defines two named sets:
WITH SET set1 AS '{allerd.eggs,allerd.soy,allerd.wheat,allerd.wheat}' 
SET set2 AS '{allerd.[dairy products],allerd.pollen,allerd.soy,allerd.wheat}' 
SELECT MEASURES.[%COUNT] ON 0, INTERSECT(set1,set2) ON 1 FROM patients
                             Patient Count
1 soy                                   462
2 wheat                                 479
In contrast, consider the following variation, which uses the ALL keyword:
WITH SET set1 AS '{allerd.eggs,allerd.soy,allerd.wheat,allerd.wheat}' 
SET set2 AS '{allerd.[dairy products],allerd.pollen,allerd.soy,allerd.wheat}' 
SELECT MEASURES.[%COUNT] ON 0, INTERSECT(set1,set2,ALL) ON 1 FROM patients
                             Patient Count
1 soy                                   462
2 wheat                                 479
3 wheat                                 479
Finally, you can of course use more interesting sets as arguments. For example:
WITH SET set1 AS 'TOPCOUNT(homed.city.members,5,MEASURES.[avg allergy count])' 
SET set2 AS 'TOPCOUNT(homed.city.members,5,MEASURES.[avg age])' 
SELECT MEASURES.[%COUNT] ON 0, INTERSECT(set1,set2) ON 1 FROM patients
                             Patient Count
1 Centerville                         1,155
2 Magnolia                            1,156

See Also

Previous section   Next section