Page 1 of 1

Combine 2 files with distinct numer of records.

PostPosted: Fri Jan 21, 2011 5:56 pm
by javivi
Hi,
I have a two files and I need to create a new file with records from file1 and file2. I will try to explain the problem and what I want with a example:

File1 (ordered descending by KEY and DATE )
KEY      DATE    AMOUNT     
AAAAA 2010-02-15 000100
AAAAA 2010-07-25 001230
AAAAA 2010-12-11 000075
BBBBB 2010-05-20 000250
BBBBB 2010-07-12 000400
BBBBB 2010-08-11 000000


File2 (ordered descending by KEY ) this file has 365 records, one by day of the 2010 year.
KEY     
2010-01-01
“   â€œ   â€œ
“   â€œ   â€œ
2010-12-31

I need to generate a new file with 365 records for every KEY of file1 with the records from file1 and inserting new records from file2 who the date don’t exist into file1. The problem is the amount that must be some time zeros and sometime the same as the last processed record from file1.

Example:

AAAAA 2010-01-01 000000 (As 2010-01-01 exist into file2 and not in file1, generate the record from key with date from file2 and amount = 0)
AAAAA 2010-01-02 000000 (same as previous explanation)ç
“ “ “
“ “ “
AAAAA 2010-02-15 000100 (as 2010-02-15 exist into file2 we generate the same record as file1)
AAAAA 2010-02-16 000100 (as 2010-02-16 exist into file2 and not in file1, generate the record from key with date from file2 and amount = to the last file1 record processed)
AAAAA 2010-02-17 000100 (same as previous explanation)
“ “ “
“ “ “
AAAAA 2010-07-25 001230 (as 2010-07-25 exist into file2 we generate the same record as file1)
AAAAA 2010-07-26 001230 (as 2010-02-16 exist into file2 and not in file1, generate the record from key with date from file2 and amount = to the last file1 record processed)
AAAAA 2010-07-28 001230 (same as previous explanation)
“ “ “
“ “ “
AAAAA 2010-12-11 000075
AAAAA 2010-12-12 000075 (as 2010-12-12 exist into file2 and not in file1, generate the record from key with date from file2 and amount = to the last file1 record processed)
AAAAA 2010-12-13 000075 (same as previous explanation)
“ “ “
“ “ “
AAAAA 2010-12-31 000075 (same as previous explanation)
BBBBB 2010-01-01 000000 (THE KEY CHANGE, As 2010-01-01 exist into file2 and not in file1, generate the record from key with date from file2 and amount = 0)
BBBBB 2010-01-02 000000 (same as previous explanation)ç
“ “ “
“ “ “
BBBBB 2010-05-20 000250 (as 2010-05-20 exist into file2 we generate the same record as file1)
BBBBB 2010-05-21 000250 (as 2010-05-21 exist into file2 and not in file1, generate the record from key with date from file2 and amount = to the last file1 record processed)
BBBBB 2010-05-22 000250 (same as previous explanation)
“ “ “
“ “ “
BBBBB 2010-07-12 000400 (as 2010-07-12 exist into file2 we generate the same record as file1)
BBBBB 2010-07-13 000400 (as 2010-02-13 exist into file2 and not in file1, generate the record from key with date from file2 and amount = to the last file1 record processed)
BBBBB 2010-07-14 000400 (same as previous explanation)
“ “ “
“ “ “
BBBBB 2010-08-11 000000 (as 2010-08-11 exist into file2 we generate the same record as file1)
BBBBB 2010-08-12 000000 (as 2010-08-12 exist into file2 and not in file1, generate the record from key with date from file2 and amount = to the last file1 record processed)
BBBBB 2010-08-13 000000 (same as previous explanation)
“ “ “
“ “ “
BBBBB 2010-12-31 000075 (same as previous explanation)

Re: Combine 2 files with distinct numer of records.

PostPosted: Fri Jan 21, 2011 7:00 pm
by NicC
LRECL? RECFM? What to do if a duplicate should occur in either file (yes- it may be impossible but the impossible has a habit of occurring)

Re: Combine 2 files with distinct numer of records.

PostPosted: Fri Jan 21, 2011 7:11 pm
by javivi
Hi NicC
lrecl=80
Recfm=FB
in this case, no duplicates in the files

Thanks

Re: Combine 2 files with distinct numer of records.

PostPosted: Sat Jan 22, 2011 3:58 am
by Frank Yaeger
You have the following mistakes in your posts:

File1 (ordered descending by KEY and DATE )
File2 (ordered descending by KEY )


Your example shows ascending order, not descending order.

BBBBB 2010-12-31 000075 (same as previous explanation)


Since you don't have a BBBBB 2010-12-31 record in file1, the amount in this output record should be 000000 from the BBBBB 2010-08-11 000000 record in file1, not 000075.

At any rate, I believe this DFSORT/ICETOOL job will do what you want:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/80)
//IN2 DD DSN=...  input file2 (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB/80)
//TOOLIN DD *
  SELECT FROM(IN1) TO(T1) ON(1,5,CH) FIRST USING(CTL1)
  COPY JKFROM TO(T2) USING(CTL2)
  COPY JKFROM TO(OUT) USING(CTL3)
/*
//CTL1CNTL DD *
  OPTION COPY
  INREC BUILD=(1,5,X)
/*
//CTL2CNTL DD *
  JOINKEYS F1=T1,FIELDS=(6,1,A),SORTED,NOSEQCK,TASKID=C1
  JOINKEYS F2=IN2,FIELDS=(11,1,A),SORTED,NOSEQCK,TASKID=C1
  REFORMAT FIELDS=(F1:1,6,F2:1,11)
  OPTION COPY
/*
//CTL3CNTL DD *
  JOINKEYS F1=IN1,FIELDS=(1,5,A,7,10,A),SORTED,TASKID=C2
  JOINKEYS F2=T2,FIELDS=(1,5,A,7,10,A),SORTED,TASKID=C2
  JOIN UNPAIRED,F2
  REFORMAT FIELDS=(F2:1,17,F1:18,6,?),FILL=C'0'
  OPTION COPY
  OUTREC IFTHEN=(WHEN=GROUP,           
    BEGIN=(24,1,CH,EQ,C'B'),           
    END=(7,10,CH,EQ,C'2010-12-31'),     
    PUSH=(18:18,6)),                   
   IFTHEN=(WHEN=INIT,BUILD=(1,23,80:X))
/*
//C1F2CNTL DD *
  INREC BUILD=(1,11,X)
/*

Re: Combine 2 files with distinct numer of records.

PostPosted: Mon Jan 24, 2011 12:46 pm
by javivi
Thanks (you have right there were 2 mistakes... :ugeek: :ugeek:

I'll try your sort and I will post the result.

Thanks again....

Re: Combine 2 files with distinct numer of records.

PostPosted: Mon Jan 24, 2011 1:42 pm
by javivi
Hi Frank

Thanks by your solution, it work perfectly and will use it in our real proyect.

Dfsort and Icetool are absolutely incredibles tools ...... :o

TVM