Compare record of two files

[patrik]

i'm looking for a simple way to compare record of two files that have the same record format; record format example:

- transaction number
- row number
- article item
- qty
- price

the file FILE01 have all the transaction number with their rows, while the FILE02 have olny one transaction with their rows; now, by reanding the FILE02 i need to show/print the records of the transaction found in the file FILE02 that are different from the file FILE01, also show/print records that are present only in the FILE01 or FILE02

FILE01                                FILE02
trans.nr row Item qty price   trans.nr row Item qty price
0100 01 AR01 1 2,33            0100 01 AR01 1 4,55 KO
0100 02 AR10 2 42,33          0100 02 AR10 2 42,33 OK
0100 03 AR10 4 25,33          0100 05 AR10 4 25,33 KO KO
0100 04 AR__ 5 25,33          0100 04 AR__ 5 25,33 OK
0100 06 ARXX 8 25,33           ___ OK

thanks in advance

[kausix777]

Re: Compare record of two files

From what I understood from your statement of the problem,

Perhaps you can think of SQL (full outer join) to do this better.

Otherwise,
take the transaction number as key;
Chain FILE02; store the field values;
Make a key list with the field values;
Chain FILE01 with the key list;
If matched, they are not unique to FILE02; else they are -> print them;
Repeat the process from FILE01 to get those records that are only in FILE01.

[MichaelCatalani]

Re: Compare record of two files

In order to see if he records are different, use a qualified data structure on the read and chain statements. When you read FIle1, read it into a qualified data structure. When you chain to file2, use a qualified data structure. Then simply compare to see if the data structures are the same or not. Any field that is different will be detected when we compare the data structures.

Here's a quick example. I didnt compile the program because I dont have the files with your fields, but you should get the idea.

fFile1     if   e           k disk                                                    
fFile2     if   e           k disk                                                    
 
d File1DS       e ds                  ExtName( File1 ) Qualified                      
d File2DS         ds                  LikeDS( File1DS )                               
 
 /free                                                                                
 
   setll *start File1;                                                                
   read File1 File1DS;                                                                
 
   dow NOT %eof( File1 );                                                             
 
     chain Trans File2 File2DS;                                                       
     if %found( File2 ) AND File1DS <> File2DS;                                       
       // ** If we are here, then there is a difference between file 1 & 2            
     endif;                                                                           
 
     read File1 File1DS;                                                              
   enddo;         
 
   *inlr = on;

[B.Hauser]

Re: Compare record of two files

Hi,

For a specific transaction you want to find out which rows are different?

If so try the following SQL-Statement:

  (   Select TransNr, Row, ArtItm, qty, price
         from File1
         where Transaction = TransNrInFile2
   Except
      Select TransNr, Row, ArtItm, qty, price
         from File2)
Union
  (   Select TransNr, Row, ArtItm, qty, price
         from File2      
   Except
      Select TransNr, Row, ArtItm, qty, price
         from File1
         where Transaction = TransNrInFile2)

Except merges 2 select statements and selects all rows that are either different or not in the second select-Statement.
Union merges 2 select statements and lists all rows retuned by the select statements but removes duplicates.

Birgitta

[Poddys]

Re: Compare record of two files

I didn't know about the EXCEPT keyword but this will achieve exactly what I want to do, a combination of your explanation here and the IBM one will do the trick nicely.

In our case we get tables exported from another system that we need to import into the AS400, and the data on these tables doesn't have a date/time last updated, every day we receive the complete table data. So if I compare the contents of each table today with the contents from yesterday, I can just process new and changed records.

The data arrives as .csv files which are uploaded into database files, so all I need to do at the start of the process is to copy the previous version of the file and to compare the two using SQL.