S A M P L E   F O C E X E C S

Unduplicated Counts

Problem: You wish to count employees in the test file by title unit code (tuc). You want to count each employee only once. Since tuc is at the appointment level, you may be counting appointments, not employees. 
A.   >
     tfs
     count empno
     end

     NUMBER OF RECORDS IN TABLE=          848   LINES=       1

     PAGE       1

     EMPNO
     COUNT
     -----
       848

     >
     tfs
     count empno by tuc
     on table column-total
     end

     NUMBER OF RECORDS IN TABLE=          1330    LINES=       3

     PAGE      1

           EMPNO
     TUC   COUNT
     ---   -----
              15
     cx     1222
     fx       60
     sx       33

  TOTAL    1330
In example A the count is unduplicated at the employee level. There are 848 employee numbers in the file. Count B is duplicated at the appointment level. There are 1330 apointments belonging to the 848 employees.

To obtain an unduplicated count, use a hold file.

  1. Determine the data elements needed for the final report.

  2. Determine data elements to the sort level desired (employee or title). Remember, integer fields will be added together. If vou do not want to collapse an integer field you can select the last occurrence by using the prefix "Lst." before the field name. Alpha-numeric fields will be collapsed to the level you indicate and only the last value will appear.

  3. Create the hold file and collapse the data element by using the "sum" or "write" command.

  4. Produce reports from the hold file. The only elements you can use in reports are those which have been held in your hold file.

  5. Remember that all headcounts from alternate file views based on appointment level data elements (tuc, appointment type) will be duplicated. You must always unduplicate headcounts in these cases.
The following hold file and report are unduplicated at the employee level. 
>
tfs
sum tuc filedate by empno
on table hold
end

NUMBER OF RECORDS  IN TABLE=    1330  LINES=   848

HOLDING    ...
>
table file hold
heading center
"tuc count effective 


Back to Sample Focexecs