IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Split file based on Sum of a particular field
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
rajinfy123

New User


Joined: 09 Jul 2004
Posts: 12
PostPosted: Mon Dec 15, 2008 5:36 am
 Reply with quote
I have a Input file with Employee-Id(4 bytes),Name(6 bytes),Code(3 bytes),Amount(S9(07)V99)
My requirement is to write records to File1 until the sum of Amount <= 400 at the Employee-Id level and Records crossing that total to be dropped to File2

Pls let me know if any more info required

Input:
1111JOHN CCC00001000{
1111PATRICDDD00001000{
1111MARY BBB00001000{
1111MARY CCC00000500{
1111MARY DDD00000500{
1111PATRICAAA00000500{
1111JOHN AAA00000500{
2222RAM CCC00000500{
2222PATTI BBB00001000{
2222RAM AAA00001000{
2222SUNIL AAA00001000{
2222PATTI CCC00001000{

Output:
File1:
1111JOHN CCC00001000{
1111PATRICDDD00001000{
1111MARY BBB00001000{
1111MARY CCC00000500{
1111MARY DDD00000500{
2222RAM CCC00001000{
2222PATTI BBB00001000{
2222RAM AAA00001000{
2222SUNIL AAA00001000{

File2:
1111PATRICAAA00000500{
1111JOHN AAA00000500{
2222PATTI CCC00001000{[/code]
Back to top
View user's profile Send private message
nelson.pandian

Active User


Joined: 09 Apr 2008
Posts: 133
Location: Phoenix, AZ
PostPosted: Mon Dec 15, 2008 11:43 am
 Reply with quote
Hi rajinfy,

There are difference between your input and output records.

INPUT record:
Code:
2222RAM   CCC00000500{
2222PATTI BBB00001000{
2222RAM   AAA00001000{
2222SUNIL AAA00001000{
2222PATTI CCC00001000{


Output records: File1
Code:
2222RAM   CCC00001000{
2222PATTI BBB00001000{
2222RAM   AAA00001000{
2222SUNIL AAA00001000{


File 2:
Code:
2222PATTI CCC00001000{


There is difference in the first record 'RAM'
If you add the input records the added value is 450. But in the case of output it is 500. Can you tell us why this difference?

While adding if the added value exceeds the limit (>400), where the record should be present. In FILE1 or FILE2. Or we have to split the records and put the remaining values in file2.

Give us a clear example with the rules.
Back to top
View user's profile Send private message
rajinfy123

New User


Joined: 09 Jul 2004
Posts: 12
PostPosted: Mon Dec 15, 2008 9:33 pm
 Reply with quote
I have changed the INPUT & OUTPUT expected now..

I have a Input file with Employee-Id(4 bytes),Name(5 bytes),Code(3 bytes),Amount(S9(07)V99)
My requirement is to write records to File1 until the sum of Amount <= 400 at the
Employee-Id level(i.e summing to be done at say '1111' ) and as soon as
adding the next Record crosses that total the records starting to be dropped to File2 starting with Employee-Id = '1111'

Pls let me know if any more info required

Input:
1111JOHN CCC00001000{
1111PATTIDDD00001000{
1111MARY BBB00001000{
1111MARY CCC00000500{
1111MARY DDD00000500{
1111PATTIAAA00000500{
1111JOHN AAA00000500{
2222PATTIBBB00001000{
2222RAM AAA00001000{
2222SUNILAAA00001000{
2222PATTICCC00001000{
2222RAM CCC00000500{

Output:
File1:
1111JOHN CCC00001000{
1111PATTIDDD00001000{
1111MARY BBB00001000{
1111MARY CCC00000500{
1111MARY DDD00000500{
2222PATTIBBB00001000{
2222RAM AAA00001000{
2222SUNILAAA00001000{
2222PATTICCC00001000{
2222RAM CCC00000500{

File2:
1111PATTIAAA00000500{
1111JOHN AAA00000500{
2222RAM CCC00000500{

Thanks,
Raj
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Wed Dec 17, 2008 5:53 am
 Reply with quote
rajinfy123,

The following DFSORT/ICETOOL JCL will give you the desired results. This job checks for a sum of 400 for every key in position 1 thru 4


Code:

//STEP0100 EXEC PGM=SORT                                   
//SYSOUT   DD SYSOUT=*                                     
//SORTIN   DD *                                             
1111JOHN CCC00001000{                                       
1111PATTIDDD00001000{                                       
1111MARY BBB00001000{                                       
1111MARY CCC00000500{                                       
1111MARY DDD00000500{                                       
1111PATTIAAA00000500{                                       
1111JOHN AAA00000500{                                       
2222PATTIBBB00001000{                                       
2222RAM  AAA00001000{                                       
2222SUNILAAA00001000{                                       
2222PATTICCC00001000{                                       
2222RAM  CCC00000500{                                       
3333XXXXXBBB00001000{                                       
3333YYYYYAAA00002300{                                       
3333ZZZZZAAA00000200{                                       
3333AAAAACCC00030000{                                       
3333BBBBBCCC00000500{                                       
//SORTOUT  DD DSN=&&I1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)         
//SREC     DD DSN=&&I2,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)
//SYSIN    DD *                                                   
 SORT FIELDS=(1,4,CH,A)                                           
 OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(23:SEQNUM,8,ZD,RESTART=(1,4))),
 IFTHEN=(WHEN=GROUP,BEGIN=(23,8,ZD,EQ,1),PUSH=(32:ID=8))           
 OUTFIL REMOVECC,NODETAIL,BUILD=(52X),                             
 SECTIONS=(1,4,23,8,                                               
 TRAILER3=(1,21,X,32,8,32:SUBTOT=(13,9,ZD,M11,LENGTH=10)))         
 OUTFIL FNAMES=SREC,REMOVECC,NODETAIL,BUILD=(52X),                 
 HEADER1=(1,4,23:8C'0',43:10C'0'),                                 
 SECTIONS=(1,4,                                                   
 TRAILER3=(1,4,23:32,8,43:SUBTOT=(13,9,ZD,M11,LENGTH=10)))         
/*       
//STEP0200 EXEC PGM=ICETOOL                                       
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//IN       DD DSN=&&I1,DISP=SHR                                   
//         DD DSN=&&I2,DISP=SHR                                   
//T1       DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)         
//OUT1     DD SYSOUT=*                                             
//OUT2     DD SYSOUT=*                                             
//TOOLIN   DD *                                                   
  SPLICE FROM(IN) TO(T1) ON(23,8,CH) WITH(1,42) WITHALL USING(CTL1)
  COPY FROM(T1) USING(CTL2)                                       
//CTL1CNTL DD *                                                   
  SORT FIELDS=(23,8,CH,A,43,10,CH,D)                               
  INREC IFTHEN=(WHEN=(43,8,CH,GT,C' '),                           
  OVERLAY=(23:+1,ADD,23,8,ZD,M11,LENGTH=8))                       
  OUTFIL FNAMES=T1,                                               
  BUILD=(1,21,X,43,10,UFF,SUB,32,10,ZD,M11,LENGTH=10)             
//CTL2CNTL DD *                                                   
  OUTFIL FNAMES=OUT1,INCLUDE=(23,10,ZD,LE,40000),BUILD=(1,21)     
  OUTFIL FNAMES=OUT2,SAVE,BUILD=(1,21)                             
/*


The output from this job is

OUT1
Code:

1111JOHN CCC00001000{
1111PATTIDDD00001000{
1111MARY BBB00001000{
1111MARY CCC00000500{
1111MARY DDD00000500{
2222PATTIBBB00001000{
2222RAM  AAA00001000{
2222SUNILAAA00001000{
2222PATTICCC00001000{
3333XXXXXBBB00001000{
3333YYYYYAAA00002300{
3333ZZZZZAAA00000200{


out2

Code:

1111PATTIAAA00000500{
1111JOHN AAA00000500{
2222RAM  CCC00000500{
3333AAAAACCC00030000{
3333BBBBBCCC00000500{
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Wed Dec 17, 2008 7:57 am
 Reply with quote
Kolusu,

Did you have a look at the required output posted by the OP? It does not seem to match with that from your job for the key '1111'.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Wed Dec 17, 2008 10:15 pm
 Reply with quote
arcvns wrote:
Kolusu,

Did you have a look at the required output posted by the OP? It does not seem to match with that from your job for the key '1111'.


arcvns,

It was a copy paste error. I've corrected my post. The OUT1 will have the all the keys which OP wants.

I forgot to mention that my solution only works for positive amounts
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts FTP VB File from Mainframe retaining ... JCL & VSAM 1
No new posts Extract the file name from another fi... DFSORT/ICETOOL 6
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us