Combine 2 files with distinct numer of records.



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Combine 2 files with distinct numer of records.

Postby javivi » Fri Jan 21, 2011 5:56 pm

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)
javivi
 
Posts: 47
Joined: Fri Jan 21, 2011 2:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Combine 2 files with distinct numer of records.

Postby NicC » Fri Jan 21, 2011 7:00 pm

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)
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Combine 2 files with distinct numer of records.

Postby javivi » Fri Jan 21, 2011 7:11 pm

Hi NicC
lrecl=80
Recfm=FB
in this case, no duplicates in the files

Thanks
javivi
 
Posts: 47
Joined: Fri Jan 21, 2011 2:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Combine 2 files with distinct numer of records.

Postby Frank Yaeger » Sat Jan 22, 2011 3:58 am

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)
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Combine 2 files with distinct numer of records.

Postby javivi » Mon Jan 24, 2011 12:46 pm

Thanks (you have right there were 2 mistakes... :ugeek: :ugeek:

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

Thanks again....
javivi
 
Posts: 47
Joined: Fri Jan 21, 2011 2:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Combine 2 files with distinct numer of records.

Postby javivi » Mon Jan 24, 2011 1:42 pm

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
javivi
 
Posts: 47
Joined: Fri Jan 21, 2011 2:53 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post