The how-to article to find duplicate records is 404.

https://techsupport.informatio...om/sps/81071020.html

I need a report that locates duplicate records in which four fields are the same. If a record contains the same values in f3,f4,f5 and f6 Then print the ENTIRE Record (24 fields). With something incase no records are found (the desired response):

IF COUNT EQ 0 Print "No Duplicates Found"

TABLE FILE ELAPSED_TIME_CSV
BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F3
BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F4
BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F5
BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F6  

I grab the four fields from the source and sort.

Not sure if I should COUNT, HOLD then do a MATCH for WHERE TOTAL GT 1?

F3 is an employee id and each emplyee ID shows up 14 times in the flat file we are reporting against (once for each day of the 2 week pay period). But if these 4 fields match, the record is a dup and can not be imported and needs to be corrected. The payroll system will not takes duplicate records and just rejects them both. Leaving the employee with no hours worked for that day.

Am I in the right direction here?

This message has been edited. Last edited by: Greg,

Since it appears all 4 fields are in the same record, why not:

 
WHERE ((F3 NE F4) AND (F4 NE F5) AND (F5 NE F6))
 

Granted, there will not be a 'No Duplicates Found' response without a little more coding, but, no record that has the same value in all 4 fields will get through.

Greg,
You say:

quote:

I need a report that locates duplicate records in which four fields are the same. If a record contains the same values in f3,f4,f5 and f6 Then print the ENTIRE Record (24 fields).

Do you mean that f3=f4=f5=f6?
Or do you mean that you have 2 or more records with the same values of f3, f4, f5, f6?

A multi-verb request like this might work, assuming duplicate values occur across records and not within records.

TABLE FILE ELAPSED_TIME_CSV
 COUNT ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F3 NOPRINT
 BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F3    NOPRINT
 BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F4    NOPRINT
 BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F5    NOPRINT
 BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F6    NOPRINT
-*
 PRINT *
 BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F3    NOPRINT
 BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F4    NOPRINT
 BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F5    NOPRINT
 BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F6    NOPRINT
 WHERE TOTAL COUNT GT 1 ;
END
-RUN
-IF (&LINES GT 0) GOTO EXIT ;
-TYPE No Duplicates Found.
-EXIT

Do you mean that f3=f4=f5=f6?
Or do you mean that you have 2 or more records with the same values of f3, f4, f5, f6?[/QUOTE]

Records with the same values in all four fields.

not f3=f4=f5=f6

ex:

F3 F4 F5 F6
115262 0 2012-09-28 1
115262 0 2012-09-28 1

This would be a dup and end up on the report.

quote:

Originally posted by Dan Satchell:
A multi-verb request like this might work, assuming duplicate values occur across records and not within records.

TABLE FILE ELAPSED_TIME_CSV
 COUNT ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F3 NOPRINT
 BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F3    NOPRINT
 BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F4    NOPRINT
 BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F5    NOPRINT
 BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F6    NOPRINT
-*
 PRINT *
 BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F3    NOPRINT
 BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F4    NOPRINT
 BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F5    NOPRINT
 BY ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F6    NOPRINT
 WHERE TOTAL COUNT GT 1 ;
END
-RUN
-IF (&LINES GT 0) GOTO EXIT ;
-TYPE No Duplicates Found.
-EXIT

This code returns a page number and the names of all the fields in the table:

PAGE 1

F1 F2 F3 F4 F5X F6 F7 F8 F9 F10 F11X F12 F13 F14 F15 F16 F17 F18 F19 F20 F21 F22 F23 F24 F25

If you get an empty report, you could try: SET EMPTYREPORT = OFF. Otherwise, you might HOLD the output, then test &LINES to see whether to print the results or display a No Duplicates message.

The falt file I am reporting against has 2 duplicates records in it for testing.

These are not being reported with the above code.

I remember the LAST function which is shown in this example.
It would allow you to compare f3 to LAST f3, f4 to LAST f4, etc. and select where there is equality in each of the four fields between two rows.

Greg,

Using the famous CAR file:

  
-* File greg3.fex
TABLE FILE CAR
SUM CNT.SALES NOPRINT
BY COUNTRY NOPRINT
BY CAR NOPRINT
PRINT COUNTRY CAR RCOST DCOST SALES
BY COUNTRY NOPRINT
BY CAR NOPRINT
IF TOTAL CNT.SALES GT 1
END

will give only the duplicates.

How about concatenating the 4 separate fields into a single field? It then become easy to COUNT the number of times a particular record occurs. If it's more than one just keep a single instance in the output file.

quote:

Originally posted by Danny-SRL:
Greg,

Using the famous CAR file:

  
-* File greg3.fex
TABLE FILE CAR
SUM CNT.SALES NOPRINT
BY COUNTRY NOPRINT
BY CAR NOPRINT
PRINT COUNTRY CAR RCOST DCOST SALES
BY COUNTRY NOPRINT
BY CAR NOPRINT
IF TOTAL CNT.SALES GT 1
END

will give only the duplicates.

This worked after I added sales as a by field.