Array concatenation operator (|)

Value1 | Value2

Value1 — ? or { ? }

The first value.

Value2 — ? or { ? }

The second value.

Returns — { ? }

An array incorporating both operands.

Joins together two arrays, or joins a value to an array, and returns the result. { 1, 2 } | 3{ 1; 2 } | 3 and { 1, 2 } | { 3 }{ 1; 2 } | { 3 } both return { 1, 2, 3 }{ 1; 2; 3 }.

This operator is specific to Calcapp.

Conditionally including an array element

To conditionally include a value in an array, this operator is not needed. This formula returns the array { 1, 2, 3 }{ 1; 2; 3 } if the value of Field1 is greater than 2, and { 1, 2 }{ 1; 2 } otherwise:

{ 1, 2, IF(Field1 > 2, 3) }{ 1; 2; IF(Field1 > 2; 3) }

This formula also works, but is not as concise:

{ 1, 2 } | IF(Field1 > 2, { 3 }, {}){ 1; 2 } | IF(Field1 > 2; { 3 }; {})

Examples

{ 1, 2 } | 3{ 1; 2 } | 3

Returns { 1, 2, 3 }{ 1; 2; 3 }.

{ 1, 2 } | { 3 }{ 1; 2 } | { 3 }

Returns { 1, 2, 3 }{ 1; 2; 3 }.

{ 1, 2, IF(Field1 > 2, 3) }{ 1; 2; IF(Field1 > 2; 3) }

Returns { 1, 2, 3 }{ 1; 2; 3 } if the value of Field1 is greater than 2, and { 1, 2 }{ 1; 2 } otherwise. This example uses IF directly in the array to conditionally include an element.

{ 1, 2 } | IF(Field1 > 2, { 3 }, {}){ 1; 2 } | IF(Field1 > 2; { 3 }; {})

Returns { 1, 2, 3 }{ 1; 2; 3 } if the value of Field1 is greater than 2, and { 1, 2 }{ 1; 2 } otherwise. This example uses the | operator to conditionally include an element, which is not as concise as using IF directly in the array.

{ 1, 2, IF(Field1 > 2, 3, BLANK()) }{ 1; 2; IF(Field1 > 2; 3; BLANK()) }

Returns { 1, 2, 3 }{ 1; 2; 3 } if the value of Field1 is greater than 2, and { 1, 2, BLANK() }{ 1; 2; BLANK() } otherwise. Specifying BLANK()BLANK() as the third parameter to IF and omitting the third parameter are typically equivalent, but differ when creating an array. Omitting the parameter omits the array element completely if the condition evaluates to FALSE, but specifying BLANK()BLANK() includes a blank value instead if the condition evaluates to FALSE.