Intersect
The MDX Intersect function for Essbase returns the intersection of two input sets, optionally retaining duplicates.
Syntax
Intersect ( set1, set2 [,ALL] )
Parameters
Notes
Duplicates are eliminated by default from the tail of the set. The optional ALL keyword retains duplicates. The two input sets must have identical dimension signatures. For example, if set1 consists of dimensions Product and Market, in that order, then set2 should also consist of Product followed by Market.
Example
Example 1
The following expression
Intersect({[New York], [California], [Oregon]},
{[California], [Washington], [Oregon]})
returns the set:
{[California], [Oregon]}
Therefore, the following query
SELECT
Intersect({[New York], [California], [Oregon]},
{[California], [Washington], [Oregon]})
ON COLUMNS
FROM Sample.Basic
returns the grid:
Table 4-84 Output Grid from MDX Example
California | Oregon |
---|---|
12964 | 5062 |
Example 2
The following expression
Intersect( { [New York], [California], [Florida], [California] },
{ [Oregon], [Washington], [California], [Florida], [California] }, ALL)
returns the set:
{ [California], [Florida], [California] }
Therefore, the following query
SELECT
Intersect( { [New York], [California], [Florida], [California] },
{ [Oregon], [Washington], [California], [Florida], [California] }, ALL)
ON COLUMNS
FROM Sample.Basic
returns the grid:
Table 4-85 Output Grid from MDX Example
California | Florida | California |
---|---|---|
12964 | 5029 | 12964 |
The matching duplicate element [California] is duplicated in the result.
However, the following expression
Intersect( { [New York], [California], [Florida], [California] },
{ [Oregon], [Washington], [California], [Florida] }, ALL)
would return only
{ [California], [Florida] }
because only one match exists between [California]
in set1 and [California]
in set2.