as_unique_list_with_null

Aggregation functions as_unique_list_with_null

syntax

  • as_unique_list_with_null(a)
  • as_unique_list_with_null(a, relation)

definition

  • as_unique_list_with_null(a) results in a Parameter with a string listing all distinct values of Attribute a — including Null — separated by “; “.
  • as_unique_list_with_null(a, relation) results in an attribute with such strings, one per partition defined by relation. The Domain unit of the resulting attribute is the Values unit of the relation. Each partition string contains the distinct values of a (including null) belonging to that partition.

description

The result per partition is a string of the form value1; value2; ..., where:

  • each distinct value occurs once, however often it occurs in a,
  • values are listed in ascending order, with null first,
  • null values in a are included and are shown as <null>.

This function is identical to as_unique_list except that null values in a are listed as well. Elements mapped to a null partition (null relation value) are still excluded from all groups.

It relates to frequency_table_with_null as as_unique_list relates to frequency_table: the same set of values, without the counts.

applies to

  • attribute a with any scalar Value type
  • relation with value type of the group CanBeDomainUnit

conditions

  1. The domain of Argument a and relation must match.

since version

20.12.0

example 

parameter<string> lifeStyleCodesWithNull := as_unique_list_with_null(City/LifeStyleCode);
// result = "<null>; 0; 1; 2"

attribute<string> lifeStyleCodesWithNullPerRegion (Region) := as_unique_list_with_null(City/LifeStyleCode, City/Region_rel);
City/LifeStyleCode City/Region_rel
2 0
0 1
1 2
0 1
1 3
1 null
null 3

domain City, nr of rows = 7

lifeStyleCodesWithNullPerRegion
“2”
“0”
“1”
“<null>; 1”
”“

domain Region, nr of rows = 5

City 6 (LifeStyleCode = null, Region_rel = 3) contributes <null> to Region 3. City 5 (Region_rel = null) is excluded from all groups. Region 1 lists 0 once, although two of its cities have that value.

see also