Join the files and based on condition need to sumup



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

Join the files and based on condition need to sumup

Postby padmaja_nan » Thu May 05, 2011 5:02 pm

Please see the example input files below
Input File 1:

COl1
AAA
BBB
CCC


Output file 2:
Col1 col2 col3
AAA Cond1 1234
AAA Cond1 4567
AAA cond2 7890
AAA Cond3 9999
AAA cond2 6666
AAA cond3 1111
BBB cond1 7777
BBB cond1 8888
BBB cond3 9999
BBB cond3 1000
CCC cond1 1111
CCC cond2 4444

Required output is :

AAA cond1 sum of (1234+4567 )
AAA cond2 sum of (7890 + 6666)
AAA cond3 sum of (9999 + 1111)
BBB ....................
CCC..............

My requirement is if the the first column of the the first file matches with the first column of the second file ,then all the all the values of third numeric field should be summed up for matching second column values .

Please help me .
padmaja_nan
 
Posts: 3
Joined: Thu May 05, 2011 4:33 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join the files and based on condition need to sumup

Postby skolusu » Thu May 05, 2011 9:25 pm

padmaja_nan,

You haven't provided the LRECL and RECFM of the input files or output files. Also You show a 4 byte zd value when summed up would result in more than 4 bytes. what is the format of the numeric field to summed up?

I assumed that both input files to be FB recfm and 80 bytes lrecl. The key to be matched is 1st 3 bytes and composite key for summing is the first 10 bytes. The numeric field to be summed is 4 bytes in zd format in pos 11.

//STEP0100 EXEC PGM=SORT                                   
//SYSOUT   DD SYSOUT=*                                     
//INA      DD *                                             
AAA                                                         
BBB                                                         
CCC                                                         
//INB      DD *                                             
AAA COND1 1234                                             
AAA COND1 4567                                             
AAA COND2 7890                                             
AAA COND3 9999                                             
AAA COND2 6666                                             
AAA COND3 1111                                             
BBB COND1 7777                                             
BBB COND1 8888                                             
BBB COND3 9999                                             
BBB COND3 1000                                             
CCC COND1 1111                                             
CCC COND2 4444                                             
//SORTOUT  DD SYSOUT=*                                     
//SYSIN    DD *                                             
  JOINKEYS F1=INA,FIELDS=(1,3,A)                           
  JOINKEYS F2=INB,FIELDS=(1,3,A)                           
  REFORMAT FIELDS=(F2:1,80)                                 
  SORT FIELDS=(1,10,CH,A)                                   
  OUTFIL REMOVECC,NODETAIL,                                 
  SECTIONS=(1,10,TRAILER3=(1,10,TOT=(11,4,ZD,M10,LENGTH=6)))
//*


The output from this job is

AAA COND1   5801
AAA COND2  14556
AAA COND3  11110
BBB COND1  16665
BBB COND3  10999
CCC COND1   1111
CCC COND2   4444
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Join the files and based on condition need to sumup

Postby padmaja_nan » Fri May 06, 2011 11:50 am

Thanks a lot Kolusu for your quick reply .
The numeric field that needs to be summed up on is S9(13)V9(2) which means it is a PD field of lenght 8 .
SO please let me know whether the below Sections line in sort card is correct fro summing up the S9(13)V9(2) field.

SECTIONS=(1,10,TRAILER3=(1,10,TOT=(11,8,PD,M16,LENGTH=16)))

And also could you please explain what is M10 and lenght in the sort card sections line .

Regards,
Padmaja
padmaja_nan
 
Posts: 3
Joined: Thu May 05, 2011 4:33 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join the files and based on condition need to sumup

Postby NicC » Fri May 06, 2011 3:56 pm

please explain what is M10 and lenght in the sort card sections line


What is it about these that you do not understand from reading the manual.
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: Join the files and based on condition need to sumup

Postby padmaja_nan » Fri May 06, 2011 4:15 pm

Hi Nicc,

For converting PD to ZD ,generally i use EDIT clause in sort card ,I would like to know about the usage of M10 or M16 ,to connvert PD to ZD and the length clause .
Could you please help me in understanding it
padmaja_nan
 
Posts: 3
Joined: Thu May 05, 2011 4:33 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join the files and based on condition need to sumup

Postby NicC » Fri May 06, 2011 4:35 pm

Could you please help me in understanding it

Nope - I would have to read the manual and perhaps play with test cases neither of which I have time for.
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: Join the files and based on condition need to sumup

Postby skolusu » Fri May 06, 2011 9:19 pm

padmaja_nan wrote:The numeric field that needs to be summed up on is S9(13)V9(2) which means it is a PD field of lenght 8 .
SO please let me know whether the below Sections line in sort card is correct fro summing up the S9(13)V9(2) field.

SECTIONS=(1,10,TRAILER3=(1,10,TOT=(11,8,PD,M16,LENGTH=16)))
And also could you please explain what is M10 and lenght in the sort card sections line .


If your numeric field starts at pos 11 then your changes are correct. M0 thru M26 are edit mask patterns. Check this link to know about the Edit Mask Patterns

The LENGTH operand is used to specify the length of the edited output field.
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post