<@INTERSECT>

Syntax

<@INTERSECT ARRAY1=arrayVarName1 ARRAY2=arrayVarName2 [COLS=compCol [compType] [, ...]] [SCOPE1=scope1] [SCOPE2=scope2]>

Description

Returns the intersection of two arrays, that is, an array containing only those rows that exist in both input arrays.

The two input arrays are not modified. To store the result of this meta tag in a variable, use a variable assignment.

The ARRAY1 and ARRAY2 attributes specify the names of variables containing arrays.The optional COLS attribute specifies the column(s) to consider when determining whether two rows are the same: the columns are specified using column numbers or names (compCol), with an optional comparison type (compType). The arrays must have the same number of columns; otherwise, an error is generated.

Valid comparison types are SMART (the default), DICT, ALPHA and NUM. DICT compares columns alphabetically, irrespective of case. ALPHA performs a case-sensitive comparison. NUM compares columns numerically. SMART checks whether values are numeric or alphabetic and performs a NUM or DICT comparison.

If no COLS attribute is specified, the intersection of the two arrays is accomplished via a SMART comparison type that examines all columns.

The SCOPE1 and SCOPE2 attributes specify the scope of the variables specified by ARRAY1 and ARRAY2, respectively. If the attribute is not specified, the default scoping rules are used.

Meta tags are permitted in any of the attributes.

Examples

• If the variable p_items contains the following array:

  red

  blue

  green

  orange

  The variable new contains the following array:

  orange

  pink

  blue

  pink

  <@INTERSECT ARRAY1="p_items" ARRAY2="new"> returns:

  blue

  orange

• If the variable test contains:

  1	a	a
  2	b	c
  3	c	c
  4	b	c

  and the variable test2 contains:

  1	a	a
  2	b	b
  3	c	c

  <@INTERSECT ARRAY1="test" ARRAY2="test2"> returns:

  1	a	a
  3	c	c

• The variable usr1 contains the following array:

  Gilbert	Steve	1823-1344	$433.00
  Brown	Robert	5543-1233	$332.50
  Brown	Marsha	1122-5778	$541.00

  The variable usr2 contains the following array:

  Kelly	Herbert	5543-1443	$100.50
  Brown	Robert	6670-1123	$1123.75

  To find users that appear in both arrays, you would find the intersection of the two arrays based on the first two columns: <@INTERSECT ARRAY1="usr1" ARRAY2="usr2" COLS="1, 2"> returns:

  Brown

  Robert

  6670-1123

  $1123.75

  Tango returns just one of the rows that have the same values in the specified columns (1 and 2).
  
  Only columns 1 and 2 are specified as relevant; the different values in the other columns are ignored for the purposes of comparison.

• In conjunction with <@IF>, <@INTERSECT> may be used to test for the existence of a row in another array. If Var_A contains the following array:

  1	John	Tesh	A
  2	Mary	Hart	B
  3	Bob	Mackie	C
  4	Sharon	Tate	D

  Var_B contains the following array:

  3

  Bob

  Mackie

  C

  <@IF EXPR="<@INTERSECT Var_A Var_B>">
  Var_B is in Var_A
  <@ELSE>
  Not in Var_A
  </@IF>

  For more information, see "Array evaluation" .

  This is because an array value specified as an expression (in <@CALC> or <@IF>) returns the number of rows in that array.

See Also

<@DISTINCT>
<@FILTER>
<@SORT>
<@UNION>