Manipulating Display Fields With Prefix Operators

In this section:

You can use prefix operators to perform calculations directly on the values of fields.

Note: Unless you change a column or ACROSS title with an AS phrase, the prefix operator is automatically added to the title. Without an AS phrase, the column title is constructed using the prefix operator and either the field name or the TITLE attribute in the Master File (if there is one):

For a list of prefix operators and their functions, see Functions You Can Perform With Prefix Operators.


Top of page

x
Prefix Operator Basics

How to:

Reference:

This topic describes basic syntax and notes for using prefix operators.



x
Syntax: How to Use Prefix Operators

Each prefix operator is applied to a single field, and affects only that field.

{SUM|COUNT} prefix.fieldname AS 'coltitle'
{PRINT|COMPUTE} RNK.byfield

where:

prefix
Is any prefix operator.
fieldname
Is the name of the field to be displayed in the report.
'coltitle'
Is the column title for the report column, enclosed in single quotation marks.
byfield
Is the name of a vertical sort field to be ranked in the report.


x
Reference: Usage Notes for Prefix Operators


x
Reference: Functions You Can Perform With Prefix Operators

The following table lists prefix operators and describes the function of each.

Prefix

Function

ASQ.

Computes the average sum of squares for standard deviation in statistical analysis.

AVE.

Computes the average value of the field.

CNT.

Counts the number of occurrences of the field. The data type of the result is always Integer.

CNT.DST.

Counts the number of distinct values within a field.

CT.

Produces a cumulative total of the specified field. This operator only applies when used in subfootings. For details, see Using Headings, Footings, Titles, and Labels.

DST.

Determines the total number of distinct values in a single pass of a data source.

FST.

Generates the first physical instance of the field. Can be used with numeric or text fields.

LST.

Generates the last physical instance of the field. Can be used with numeric or text fields.

MAX. 

Generates the maximum value of the field.

MIN.

Generates the minimum value of the field.

PCT.

Computes a field percentage based on the total values for the field. The PCT operator can be used with detail as well as summary fields.

PCT.CNT.

Computes a field percentage based on the number of instances found. The format of the result is always F6.2 and cannot be reformatted.

RNK.

Ranks the instances of a BY sort field in the request. Can be used in PRINT commands, COMPUTE commands, and IF or WHERE TOTAL tests.

RPCT.

Computes a field percentage based on the total values for the field across a row.

ST.

Produces a subtotal value of the specified field at a sort break in the report. This operator only applies when used in subfootings. For details, see Using Headings, Footings, Titles, and Labels.

SUM.

Sums the field values.

TOT.

Totals the field values for use in a heading (includes footings, subheads, and subfoots).



x
Averaging Values of a Field

The AVE. prefix computes the average value of a particular field. The computation is performed at the lowest sort level of the display command. It is computed as the sum of the field values within a sort group divided by the number of records in that sort group. If the request does not include a sort phrase, AVE. calculates the average for the entire report.



Example: Averaging Values of a Field

This request calculates the average number of education hours spent in each department.

TABLE FILE EMPLOYEE
SUM AVE.ED_HRS BY DEPARTMENT
END

The following shows the output of the request.

            AVE   
DEPARTMENT  ED_HRS
----------  ------
MIS          38.50
PRODUCTION   20.00

Top of page

x
Averaging the Sum of Squared Fields

The ASQ. prefix computes the average sum of squares, which is a component of the standard deviation in statistical analysis (shown as a formula in the following image).

If the field format is integer and you get a large set of numbers, the ASQ. result may be negative as a result of field overflow.



Example: Averaging the Sum of Squared Fields

This request calculates the sum and the sum of squared fields for the DELIVER_AMT field.

TABLE FILE SALES
SUM DELIVER_AMT AND ASQ.DELIVER_AMT
BY CITY
END

The following shows the output of the request.

                              ASQ        
CITY             DELIVER_AMT  DELIVER_AMT
----             -----------  -----------
NEW YORK                 300          980
NEWARK                    60          900
STAMFORD                 430         3637
UNIONDALE                 80         1600

Top of page

x
Calculating Maximum and Minimum Field Values

The prefixes MAX. and MIN. produce the maximum and minimum values, respectively, within a sort group. If the request does not include a sort phrase, MAX. and MIN. produce the maximum and minimum values for the entire report.



Example: Calculating Maximum and Minimum Field Values

This report request calculates the maximum and minimum values of SALARY.

TABLE FILE EMPLOYEE
SUM MAX.SALARY AND MIN.SALARY
END

The following shows the output of the request.

    MAX              MIN   
    SALARY           SALARY
    ------           ------
$29,700.00        $8,650.00

Top of page

x
Calculating Column and Row Percentages

For each individual value in a column, PCT. calculates what percentage that field makes up of the column total value. You can control how values are distributed down the column by sorting the column using the BY phrase. The new column of percentages has the same format as the original field.

You can also determine percentages for row values. For each individual value in a row that has been sorted using the ACROSS phrase, the RPCT. operator calculates what percentage it makes up for the total value of the row. The percentage values have the same format as the original field.



Example: Calculating Column Percentages

To calculate each employee share of education hours, issue the following request:

TABLE FILE EMPLOYEE
SUM ED_HRS PCT.ED_HRS BY LAST_NAME
ON TABLE COLUMN-TOTAL
END

The output is:

                         PCT   
LAST_NAME        ED_HRS  ED_HRS
---------        ------  ------
BANNING             .00     .00
BLACKWOOD         75.00   21.37
CROSS             45.00   12.82
GREENSPAN         25.00    7.12
IRVING            30.00    8.55
JONES             50.00   14.25
MCCOY               .00     .00
MCKNIGHT          50.00   14.25
ROMANS             5.00    1.42
SMITH             46.00   13.11
STEVENS           25.00    7.12
                               
TOTAL            351.00  100.00

Since PCT. and RPCT. take the same format as the field, the column may not always total exactly 100 because of the nature of floating-point arithmetic.



Example: Calculating Row Percentages

The following request calculates the total units sold for each product (UNIT_SOLD column), and the percentage that total makes up in relation to the sum of all products sold (RPCT.UNIT_SOLD column) in each city.

TABLE FILE SALES
SUM UNIT_SOLD RPCT.UNIT_SOLD ROW-TOTAL
BY PROD_CODE
ACROSS CITY    WHERE
CITY EQ 'NEW YORK' OR 'STAMFORD'
END

The output is:

Because UNIT_SOLD has an integer format, the columns created by RPCT. also have integer (I) formats. Therefore, individual percentages may be truncated and the total percentage may be less than 100%. If you require precise totals, redefine the field with a format that declares decimal places (D, F).


Top of page

x
Producing a Direct Percent of a Count

When counting occurrences in a file, a common reporting need is determining the relative percentages of each row’s count within the total number of instances. You can do this, for columns only, with the following syntax:

PCT.CNT.fieldname

The format is a decimal value of six digits with two decimal places (F6.2).



Example: Producing a Direct Percent of a Count

This request illustrates the relative percentage of the values in the EMP_ID field for each department.

TABLE FILE EMPLOYEE
SUM PCT.CNT.EMP_ID
BY DEPARTMENT
END

The output is:

PCT.CNT
DEPARTMENT
----------
 EMP_ID
 ------
MIS
  50.00
PRODUCTION
  50.00


x
Aggregating and Listing Unique Values

How to:

Reference:

The distinct prefix operator (DST.) may be used to aggregate and list unique values of any data source field. Similar in function to the SQL COUNT, SUM, and AVG(DISTINCT col) column functions, it permits you to determine the total number of distinct values in a single pass of the data source.

The DST. operator can be used with the SUM, PRINT or COUNT commands, and also in conjunction with the aggregate prefix operators SUM., CNT., and AVE. Multiple DST. operators are supported in TABLE and TABLEF requests. They are supported in requests that use the BY, ACROSS, and FOR phrases.

Note that in a request using the PRINT command and multiple DST operators, you should issue the command SET PRINTDST=NEW. For more information, see the Developing Reporting Applications manual.



x
Syntax: How to Use the Distinct Operator
command DST.fieldname

or

SUM [operator].DST.fieldname

where:

command
Is SUM, PRINT, or COUNT.
DST.
Indicates the distinct operator.
fieldname
Indicates the display-field object or field name.
operator
Indicates SUM., CNT., or AVE.


Example: Using the Distinct Operator

The procedure requesting a count of unique ED_HRS values is either:

TABLE FILE EMPLOYEE
SUM CNT.DST.ED_HRS
END

or

TABLE FILE EMPLOYEE
COUNT DST.ED_HRS
END

The output is:

COUNT   
DISTINCT
ED_HRS  
--------
       9

Notice that the count excludes the second records for values 50.00, 25.00, and .0, resulting in nine unique ED_HRS values.



Example: Counting Distinct Field Values With Multiple Display Commands

The following request against the GGSALES data source counts the total number of records by region, then the number of records, distinct categories, and distinct products by region and by state. The DST or CNT.DST operator can be used only with the last display command:

TABLE FILE GGSALES                     
COUNT CATEGORY AS 'TOTAL,COUNT'        
  BY REGION                            
SUM CNT.CATEGORY AS 'STATE,COUNT'      
    CNT.DST.CATEGORY    CNT.DST.PRODUCT
  BY REGION                            
  BY ST                                
END

The output is:

                                  COUNT     COUNT   
             TOTAL         STATE  DISTINCT  DISTINCT
Region       COUNT  State  COUNT  CATEGORY  PRODUCT 
------       -----  -----  -----  --------  --------
Midwest       1085  IL       362         3         9
                    MO       361         3         9
                    TX       362         3         9
Northeast     1084  CT       361         3        10
                    MA       360         3        10
                    NY       363         3        10
Southeast     1082  FL       361         3        10
                    GA       361         3        10
                    TN       360         3        10
West          1080  CA       721         3        10
                    WA       359         3        10


x
Reference: Distinct Operator Limitations

Top of page

x
Retrieving First and Last Records

FST. is a prefix that displays the first retrieved record selected for a given field. LST. displays the last retrieved record selected for a given field.

When using the FST. and LST. prefix operators, it is important to understand how your data source is structured.

For more information on segment types and file design, see the Describing Data With WebFOCUS Language manual. If you wish to reorganize the data in the data source or restructure the data source while reporting, see Improving Report Processing.



Example: Retrieving the First Record

The following request retrieves the first logical record in the EMP_ID field:

TABLE FILE EMPLOYEE
SUM FST.EMP_ID
END

The output is:

FST      
EMP_ID   
------   
071382660


Example: Segment Types and Retrieving Records

The EMPLOYEE data source contains the DEDUCT segment, which orders the fields DED_CODE and DED_AMT from lowest value to highest value (segment type of S1). The DED_CODE field indicates the type of deduction, such as CITY, STATE, FED, and FICA. The following request retrieves the first logical record for DED_CODE for each employee:

TABLE FILE EMPLOYEE
SUM FST.DED_CODE
BY EMP_ID
END

The output is:

           FST     
EMP_ID     DED_CODE
------     --------
071382660  CITY    
112847612  CITY    
117593129  CITY    
119265415  CITY    
119329144  CITY    
123764317  CITY    
126724188  CITY    
219984371  CITY    
326179357  CITY    
451123478  CITY    
543729165  CITY    
818692173  CITY

Note, however, the command SUM LST.DED_CODE would have retrieved the last logical record for DED_CODE for each employee.

If the record is in a segment with values organized from highest to lowest (segment type SH1), the first logical record that the FST. prefix operator retrieves is the highest value in the set of values. The LST. prefix operator would therefore retrieve the lowest value in the set of values.

For example, the EMPLOYEE data source contains the PAYINFO segment, which orders the fields JOBCODE, SALARY, PCT_INC, and DAT_INC from highest value to lowest value (segment type SH1). The following request retrieves the first logical record for SALARY for each employee:

TABLEF FILE EMPLOYEE
SUM FST.SALARY
BY EMP_ID
END

The output is:

                    FST   
EMP_ID              SALARY
------              ------
071382660       $11,000.00
112847612       $13,200.00
117593129       $18,480.00
119265415        $9,500.00
119329144       $29,700.00
123764317       $26,862.00
126724188       $21,120.00
219984371       $18,480.00
326179357       $21,780.00
451123478       $16,100.00
543729165        $9,000.00
818692173       $27,062.00

However, the command SUM LST.SALARY would have retrieved the last logical record for SALARY for each employee.


Top of page

x
Summing and Counting Values

You can count occurrences and summarize values with one display command using the prefix operators CNT., SUM., and TOT. Just like the COUNT command, CNT. counts the occurrences of the field it prefixes. Just like the SUM command, SUM. sums the values of the field it prefixes. TOT. sums the values of the field it prefixes when used in a heading (including footings, subheads, and subfoots).



Example: Counting Values With CNT

The following request counts the occurrences of PRODUCT_ID, and sums the value of UNIT_PRICE.

TABLE FILE GGPRODS
SUM CNT.PRODUCT_ID AND UNIT_PRICE
END

The output is:

Product          
Code        Unit 
COUNT       Price
-------     -----
     10    660.00


Example: Summing Values With SUM

The following request counts the occurrences of PRODUCT_ID, and sums the value of UNIT_PRICE.

TABLE FILE GGPRODS
COUNT PRODUCT_ID AND SUM.UNIT_PRICE
END

The output is:

Product          
Code        Unit 
COUNT       Price
-------     -----
     10    660.00


Example: Summing Values With TOT

The following request uses the TOT prefix operator to show the total of current salaries for all employees.

TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY DEPARTMENT
ON TABLE SUBFOOT
"Total salaries equal: <TOT.CURR_SAL"
END

The output is:

DEPARTMENT  LAST_NAME                
----------  ---------                
MIS         SMITH                    
            JONES                    
            MCCOY                    
            BLACKWOOD                
            GREENSPAN                
            CROSS                    
PRODUCTION  STEVENS                  
            SMITH                    
            BANNING                  
            IRVING                   
            ROMANS                   
            MCKNIGHT                 
Total salaries equal:     $222,284.00

Top of page

x
Ranking Sort Field Values With RNK.

How to:

RANKED BY fieldname, when used in a sort phrase in a TABLE request, not only sorts the data by the specified field, but assigns a RANK value to the instances. The RNK. prefix operator also calculates the rank while allowing the RANK value to be printed anywhere on the page. You use this operator by specifying RNK.fieldname, where fieldname is a BY field in the request.

The ranking process occurs after selecting and sorting records. Therefore, the RNK. operator cannot be used in a WHERE or IF selection test or in a virtual (DEFINE) field. However, RNK.fieldname can be used in a WHERE TOTAL or IF TOTAL test or in a calculated (COMPUTE) value. You can change the default column title for the rank field using an AS phrase.

You can apply the RNK. operator to multiple sort fields, in which case the rank for each BY field is calculated within its higher level BY field.



x
Syntax: How to Calculate Ranks Using the RNK. Prefix Operator

In a PRINT command, COMPUTE expression, or IF/WHERE TOTAL expression :

RNK.field  ...

where:

field
Is a vertical (BY) sort field in the request.


Example: Ranking Within Sort Groups

The following request ranks years of service within department and ranks salary within years of service and department. Note that years of service depends on the value of TODAY. The output for this example was valid when run in September, 2006:

DEFINE FILE EMPDATA
  TODAY/YYMD = &YYMD;
  YRS_SERVICE/I9 = DATEDIF(HIREDATE,TODAY,'Y');
END
TABLE FILE EMPDATA
PRINT SALARY
  RNK.YRS_SERVICE AS 'RANKING,BY,SERVICE'
  RNK.SALARY AS 'SALARY,RANK'
     BY DEPT
     BY HIGHEST YRS_SERVICE
     BY HIGHEST SALARY NOPRINT
WHERE DEPT EQ 'MARKETING' OR 'SALES'
ON TABLE SET PAGE NOPAGE
END

The output is:

                                                    RANKING
                                                    BY       SALARY
DEPT                  YRS_SERVICE           SALARY  SERVICE  RANK
----                  -----------           ------  -------  ------
MARKETING                      17       $55,500.00        1       1
                                        $55,500.00        1       1
                               16       $62,500.00        2       1
                                        $62,500.00        2       1
                                        $62,500.00        2       1
                                        $58,800.00        2       2
                                        $52,000.00        2       3
                                        $35,200.00        2       4
                                        $32,300.00        2       5
                               15       $50,500.00        3       1
                                        $43,400.00        3       2
SALES                          17      $115,000.00        1       1
                                        $54,100.00        1       2
                               16       $70,000.00        2       1
                                        $43,000.00        2       2
                               15       $43,600.00        3       1
                                        $39,000.00        3       2
                               15       $30,500.00        3       3


Example: Using RNK. in a WHERE TOTAL Test

The following request displays only those rows in the highest two salary ranks within the years of service category. Note that years of service depends on the value of TODAY. The output for this example was valid when run in September, 2006:

DEFINE FILE EMPDATA
  TODAY/YYMD = &YYMD;
  YRS_SERVICE/I9 = DATEDIF(HIREDATE,TODAY,'Y');
END
TABLE FILE EMPDATA
PRINT LASTNAME FIRSTNAME RNK.SALARY
BY HIGHEST YRS_SERVICE BY HIGHEST SALARY
WHERE TOTAL RNK.SALARY LE 2
END

The output is:

                                                           RANK
YRS_SERVICE           SALARY  LASTNAME         FIRSTNAME   SALARY
-----------           ------  --------         ---------   ------
         17      $115,000.00  LASTRA           KAREN            1
                  $80,500.00  NOZAWA           JIM              2
         16       $83,000.00  SANCHEZ          EVELYN           1
                  $70,000.00  CASSANOVA        LOIS             2
         15       $62,500.00  HIRSCHMAN        ROSE             1
                              WANG             JOHN             1
                  $50,500.00  LEWIS            CASSANDRA        2


Example: Using RNK. in a COMPUTE Command

The following request sets a flag to Y for records in which the salary rank within department is less than or equal to 5 and the rank of years of service within salary and department is less than or equal to 6. Otherwise, the flag has the value N. Note that the years of service depends on the value of TODAY. The output for this example was valid when run in September, 2006:

DEFINE FILE EMPDATA
  TODAY/YYMD = &YYMD;
  YRS_SERVICE/I9 = DATEDIF(HIREDATE,TODAY,'Y');
END
PRINT RNK.SALARY RNK.YRS_SERVICE
COMPUTE FLAG/A1 = IF RNK.SALARY LE 5  AND RNK.YRS_SERVICE LE 6
    THEN 'Y' ELSE 'N';
BY DEPT BY SALARY BY YRS_SERVICE
WHERE DEPT EQ 'MARKETING' OR 'SALES'
ON TABLE SET PAGE NOPAGE
END

The output is:

                                                 RANK   RANK
DEPT                          SALARY YRS_SERVICE SALARY YRS_SERVICE FLAG
----                          ------ ----------- ------ ----------- ----
MARKETING                 $32,300.00          16      1           1 Y
                          $35,200.00          16      2           1 Y
                          $43,400.00          15      3           1 Y
                          $50,500.00          15      4           1 Y
                          $52,000.00          16      5           1 Y
                          $55,500.00          17      6           1 N
                                                      6           1 N
                          $58,800.00          16      7           1 N
                          $62,500.00          16      8           1 N
                                                      8           1 N
                                                      8           1 N
SALES                     $30,500.00          15      1           1 Y
                          $39,000.00          15      2           1 Y
                          $43,000.00          16      3           1 Y
                          $43,600.00          15      4           1 Y
                          $54,100.00          17      5           1 Y
                          $70,000.00          16      6           1 N
                         $115,000.00          17      7           1 N

WebFOCUS