How to: Reference: |
COMPUTE commands can be included in Master Files and referenced in subsequent TABLE requests, enabling you to build expressions once and use them in multiple requests.
COMPUTE fieldname/fmt=expression;
where:
Is name of the calculated field.
Is the format and length of the calculated field.
Is the formula for calculating the value of the field.
In all instances, COMPUTEs in the Master File have the same functionality and limitations as temporary COMPUTEs. Specifically, fields computed in the Master File must follow these rules:
Note:Maintain does not currently support using COMPUTEs in Master Files, and these COMPUTEs do not appear in the Update Assist Wizard.
Use standard COMPUTE syntax to add a calculated value to your Master File. You can then access the calculated value by referencing the computed fieldname in subsequent TABLE requests. When used as a verb object, as in the following example, the syntax is SUM (or PRINT) COMPUTE field.
The following is the SALESTES Master File (the SALES FILE modified with an embedded COMPUTE):
FILENAME=SALESTES, SUFFIX=FOC, SEGNAME=STOR_SEG, SEGTYPE=S1, FIELDNAME=STORE_CODE, ALIAS=SNO, FORMAT=A3, $ FIELDNAME=CITY, ALIAS=CTY, FORMAT=A15, $ FIELDNAME=AREA, ALIAS=LOC, FORMAT=A1, $ SEGNAME=DATE_SEG, PARENT=STOR_SEG, SEGTYPE=SH1, FIELDNAME=DATE, ALIAS=DTE, FORMAT=A4MD, $ SEGNAME=PRODUCT, PARENT=DATE_SEG, SEGTYPE=S1, FIELDNAME=PROD_CODE, ALIAS=PCODE, FORMAT=A3, FIELDTYPE=I, $ FIELDNAME=UNIT_SOLD, ALIAS=SOLD, FORMAT=I5, $ FIELDNAME=RETAIL_PRICE, ALIAS=RP, FORMAT=D5.2M, $ FIELDNAME=DELIVER_AMT, ALIAS=SHIP, FORMAT=I5, $ FIELDNAME=OPENING_AMT, ALIAS=INV, FORMAT=I5, $ FIELDNAME=RETURNS, ALIAS=RTN, FORMAT=I3, MISSING=ON, $ FIELDNAME=DAMAGED, ALIAS=BAD, FORMAT=I3, MISSING=ON, $ COMPUTE REVENUE/D12.2M=UNIT_SOLD*RETAIL_PRICE;
The following TABLE request uses the REVENUE field:
TABLE FILE SALESTES HEADING CENTER "NEW YORK PROFIT REPORT" " " SUM UNIT_SOLD AS 'UNITS,SOLD' RETAIL_PRICE AS 'RETAIL_PRICE' COMPUTE REVENUE; BY PROD_CODE AS 'PROD,CODE' WHERE CITY EQ 'NEW YORK' END
The output is:
NEW YORK PROFIT REPORT PROD UNITS CODE SOLD RETAIL_PRICE REVENUE ---- ---- ------------ ------- B10 30 $.85 $25.50 B17 20 $1.89 $37.80 B20 15 $1.99 $29.85 C17 12 $2.09 $25.08 D12 20 $2.09 $41.80 E1 30 $.89 $26.70 E3 35 $1.09 $38.15
Information Builders |