Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Split file based on Sum of a particular field

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Split file based on Sum of a particular field
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    Post subject:
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    Post subject:
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    Post subject: Reply to: Split file based on Sum of a particular field
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: 2238
Location: @my desk

PostPosted: Wed Dec 17, 2008 7:57 am    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Changing of LRECL of a file abdulrafi DFSORT/ICETOOL 2 Fri Mar 24, 2017 3:25 pm
No new posts splitting a file abdulrafi DFSORT/ICETOOL 4 Fri Mar 24, 2017 11:51 am
No new posts Receive a file using PCOMM macro Harald.v.K IBM Tools 0 Thu Mar 23, 2017 6:50 pm
No new posts Export flat file data into excel sheet murali.andaluri DFSORT/ICETOOL 2 Mon Mar 20, 2017 5:39 pm
No new posts Formatting VB File Learncoholic DFSORT/ICETOOL 3 Mon Mar 20, 2017 12:29 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us