User's Manual

116
Chapter 7
You can also use a number of counting functions to obtain counts of values that me et specific
criteria , eve
n w he n th ose values are stored in multiple fields. For example, to count the number of
cards that have been held for m ore than five y ears:
count_greater_than(5, ['cardtenure' 'card2tenure' 'card3tenure'])
To count null v alues across the same set of fields:
count_nulls(['cardtenure' 'card2tenure' 'card3tenure'])
Note that this examp le counts the nu mber of cards being held, not the number of people holding
them. For more information, see the topic Comparison Functions in Chapte r 8 on p. 135.
To count the number of times a specified value o ccurs across multiple fields, you can use the
count_equal function. The f ollow ing example counts the number 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 retu r ns the results for the value Y
as shown.
Answer1 Answer2 Answer3 Count
Y N Y
2
Y N N
1
Numeric Functions
You can obtain statistics 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 to pic Numeric Functions in Chapter 8 on p. 138.
Generating Lists of Fields
When usin g any of the functions that accept a list of fields as input , the special functions
@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, to coun t the number of null values across a ll fields beginning with “card”:
count_nulls(@FIELDS_MATCHING('card*'))
For more information, see the topic Spec ial Fields in Chapter 8 on p. 157.