116
Chapter 7
Youcan also use a num ber of counting functions to obtain counts of values that meet specific
criteria, even when those values are stored in multiple fields. For example, t oc ount the number of
cards that have been held for more than five years:
count_greater_than(5,['cardtenure' 'card2tenure' 'card3ten ure'])
Tocount n ull values across the same set of fields:
count_nulls(['cardtenure''card2tenure' 'card3tenure'])
Note that this example counts the number of cards being held, not the number of people holding
them. For more information, see the topic Comp arison Functions in Chapter 8 on p. 135.
Tocount the n umber of times a specified value occurs across multiple fields, you can use the
count_equalfunct ion. The following example counts the numb er of fields in the list that contain
the value Y.
count_equal("Y",[Answer1,Answer2, Answer3])
Given the following values for the fields in the list, the function returns the results for the value Y
as shown.
Answer1 Answer2 Answer3 Count
Y N Y 2
Y N N 1
Numeric Functions
Youcan obta ins tatistics across multiple fields using the sum_n,mean_n, and sdev_n
functions—for example:
sum_n(['card1bal''card2bal''card3bal'])
mean_n(['card1bal''card2bal''card3bal'])
For more information, see the topic Numeric Functions in Chapter 8 on p. 138.
Generating Lists of Fields
When using any of the functions that accept a list of fields as input, the special functio ns
@FIELDS_BETWEEN(start, end) and @FIELDS_MATCHING(pattern) can be used as input. For
example, assuming the order of fields is as shown in the sum_n example earlier, the following
would be equivalent:
sum_n(@FIELDS_BETWEEN(card1bal,card3bal))
Alternatively,t o count the number of null values across all fields beginning with “card”:
count_nulls(@FIELDS_MATCHING('card*'))
For more information, see the topic Special Fields in Chapter 8 on p. 157.