Exercise: Using the Intersect Function

The Intersect function returns the intersection two input sets, optionally retaining duplicates. Syntax:

Intersect (set, set [,ALL])

Use the Intersect function to compare sets by finding tuples that are present in both sets.

  To use the Intersect function:

  1. Open qry_blank.txt.

  2. Delete the braces {} from the axis, and replace them with Intersect() . For example:

    SELECT
       Intersect (
    
       )
    ON COLUMNS
    FROM Sample.Basic
  3. Add two comma-separated pairs of braces to use as placeholders for the two set arguments you will provide to the Intersect function. For example:

    SELECT
       Intersect ( 
       { },
       { }
       )
    ON COLUMNS
    FROM Sample.Basic
  4. Specify children of East as the first set argument. For example:

    SELECT
       Intersect ( 
       { [East].children },
       { }
       )
    ON COLUMNS
    FROM Sample.Basic
  5. For the second set argument, specify all members of the Market dimension that have a UDA of “Major Market.” For example:

    SELECT
       Intersect (
       { [East].children },
       { UDA([Market], "Major Market") }
       )
    ON COLUMNS
    FROM Sample.Basic
  6. Save the query as qry_intersect_func.txt.

  7. Paste the query into the MaxL Shell and run it, as described in Exercise: Running Your First Query.

Results: All children of East that have a UDA of “Major Market” are returned. For example:

New York   Massachusetts   Florida


8202       6172            5029