One way to ensure the integrity of the data is to check for duplicate records. To find the total amount of a field, use the Total command. The Quick Sort feature lets you view the data in sequential order. A filter is a logical expression that you can type in the view’s Filter text box. CAL displays only those records that match the filter. An expression is a set of operators and values that you use to perform calculations, specify conditions for a test, or create values that do not exist directly in the data. An CAL expression can be a combination of data fields or computed fields, operators,constants, functions, and variables.
Commands: Statistics, Profile, Duplicate, Total, Quick Sort— See the detailed explanations below. In this chapter you accomplished the following tasks: ; Opening a project Viewing the project contents ; Using Statistics and Profile commands to analyze data ; Using Quick Sort to sort the data in a specified order Creating and applying filters Examine the employee list Tasks: View the employee data project. Get statistical picture. Check for duplicate employee records. Open and view the employee data project Open the employee data project
Project: Open an existing and save project as Surname_Metaphor_Employee_Data, then K, and close project. Open an existing project—Surname_Metaphor_Employee_Data and start. Tables: Agents_Metaphor – lists all of Metaphor’s independent sales agents around the world. Rename this file as Surname_Agents_Metaphor. Employee_List – lists all of the Metaphor employees at the head office. Rename this file as Surname_Employee_list. View the contents of the employee data project (Insert your answer here) Get a statistical picture Generate statistics on the employee list Use Profile to get information about a table
Look for duplicate records Look for duplicate employee numbers Examine employee salaries and Bonuses Find total bonuses paid in 2002 Use the Total command to find total bonuses paid in 2002 View salaries in descending and ascending order View employee salaries in descending and ascending order View employees hired in 2002 Create a filter to show employees that were hired in 2002 Determine bonus as a percentage of salary Your Log Your Folder Explanations: STATISTICS Syntax numeric-field-list specifies the numeric fields or expressions for which statistics will be provided. Et-field-list specifies the date fields or expressions for which statistics will be provided. SST calculates the standard deviation of the fields specified, in addition to the other statistics. Obtaining statistics on numeric and date fields Use Statistics on date and numeric fields to obtain an overview of the data. For specified fields, the Statistics command provides: Record counts, field totals, and average field values for positive, nil, and negative field values Absolute value Ranges Highest and lowest field values The Statistics command also populates a number of system variables. To use the
Statistics command Select Analyze В» Statistical В» Statistics to display the Statistics dialog box. PROFILE PROFILE numeric fields numeric_fields specifies the numeric fields to be profiled. Producing summary statistics on numeric fields Use the Profile command to provide summary statistics on one or more numeric fields in a table. Profile provides the following information for numeric fields: Total value Absolute value Minimum value Maximum value Profile is used primarily to determine minimum, maximum, absolute, and total field values (as appropriate) before issuing the Stratify, Histogram, or Sample commands.
If you want an overview of a table before detailed processing, the Statistics command provides more complete information. After a field has been profiled, CAL knows its minimum and maximum values. These values are then used as defaults for the Minimum and Maximum parameters in the Stratify and Histogram commands. Similarly,
The profiles can be viewed in the command results. To turn this option on, select Tools from the menu bar and choose Options. Click the Table tab and select Automatically Profile on Open. To use the Profile command Select Analyze В»Statistical В»Profile to display the Profile dialog box. DUPLICATES DUPLICATES key_field . ON key-field specifies the fields or expressions to check for duplicates. UNFORMATTED suppresses page headings and page breaks when the output is directed to a file. OTHER field-list specifies a list of fields or expressions to include in the output for a duplicates test.
The fields are reported in columnar format with duplicate items displayed in grouped rows. A blank line separates each set of duplicates. The duplicated key field values are reported in the first column, followed by the specified fields. PRESORT sorts the primary table on the primary key before testing for duplicates. You cannot use the PRESORT keyword in a group. Testing for duplicates The Duplicates command detects whether key fields in the active table contain duplicates. To test for duplicate records Select Analyze from the menu bar and choose Look for Duplicates to display the
Duplicates dialog box. If your table contains a great many records, it is faster and more useful to save the command results to a file than to display the results on the screen. TOTAL TOTAL numeric fields Totaling numeric fields or expressions Use the Total command to total numeric fields or expressions in the active table. Total is typically used to prove the completeness and accuracy of the data and to produce control totals. Total finds the arithmetic sum of the fields or expressions specified. As well as displaying the totals requested, this command saves the result n a special variable, Totals. Is normally “1”, but adopts the sequential command number when Total is issued in a group, for example, TOTAL 17. To use the Total command Select Analyze from the menu bar and choose Total Fields to display the Total dialog box. Sorting a view with Quick Sort You can use Quick Sort to sort records on any field. Applicable only to the view, Quick Sort is a temporary means to view your records in ascending or descending order. When you are finished with Quick Sort, you can turn it off to return the view to the order in which the records exist in the table.
To use Quick Sort, right-click the column header and select one of the sort options: Quick Sort Ascending – sorts the records from lowest to highest. Quick Sort Descending – sorts the records from highest to lowest. Quick Sort Off – restores the records to the previous sort order. Quick Sort applies to the view only and not to commands, which use the sort order that is present in the table. To sort the records in the table, use the Sort command. To index the table by one or more fields, use the Index command. Quick Sort is not available for dynamic server-based CAL tables.