Merging 2 files whlie summing together duplicate records



JES, JES2, JCL utilities, IDCAMS, Compile & Run JCLs, PROCs etc...

Merging 2 files whlie summing together duplicate records

Postby D9988 » Thu May 08, 2008 11:53 pm

Hello. I've looked through some similar posts here, as well as checking out publibz.boulder.ibm.com. I'm not sure if this can be done via JCL commands. Any feedback would be appreciated.

I have 2 similar files. One is a running total. One is a daily total. Each evening, I want to update the running total with data from the daily total. For example (the column headings aren't actually on the file):

Daily Total
Name                      Apples             Oranges          Bananas
Joe Smith                      1                   1                2
John Smith                     1                   0                2
Michelle Smith                 0                   2                0         


Running Total
 
Name                      Apples             Oranges          Bananas
Joe Smith                    10                   10                10
John Smith                   5                    5                 0


If there are any matcing records between the 2 files, Joe and John Smith for example, I want to combine the total's together (leaving one record on the Running Totals file). If any record is present on the Daily Total file, but not on the Running Total file, I want to add it to the Running Total file. Sample output would be:

updated Running Total

Name                      Apples             Oranges          Bananas
Joe Smith                     11                   11                   12
John Smith                     6                    5                    0
Michelle Smith                 0                    2                    0


Is this possible? Would it be a SORT statement utilizing SUM FIELDS?
Thanks
David
D9988
 
Posts: 34
Joined: Fri Nov 09, 2007 10:55 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merging 2 files whlie summing together duplicate records

Postby Frank Yaeger » Fri May 09, 2008 12:53 am

Would it be a SORT statement utilizing SUM FIELDS?


You would concatenate the two files as SORTIN and use a SORT statement for the Name field and a SUM statement for the fields to be summed.

The exact statements depend on the starting position, length and format of the Name, Apples, Oranges and Bananas fields. If you want more specific help with this, give me that information.

BTW, the output for John Smith/Bananas should be 2 rather than 0 ... right?
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: Merging 2 files whlie summing together duplicate records

Postby D9988 » Fri May 09, 2008 2:30 am

Frank Yaeger wrote:
BTW, the output for John Smith/Bananas should be 2 rather than 0 ... right?


Nice catch, my friend! I think I can figure it out from here, thanks for the feedback. :D
David
D9988
 
Posts: 34
Joined: Fri Nov 09, 2007 10:55 pm
Has thanked: 0 time
Been thanked: 0 time


Return to JCL

 


  • Related topics
    Replies
    Views
    Last post