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
 

 

Sum key group and remove if zeroes

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Andy85

New User


Joined: 25 Dec 2008
Posts: 35
Location: India

PostPosted: Tue Jan 05, 2010 11:52 am    Post subject: Sum key group and remove if zeroes
Reply with quote

Hi,

I have a requirement in which the input file contains multiple records for same key. I need to find out the sum of an amount field in this file, grouped on key (except for particular record type). If the sum is zeroes, then all the records for that key should be removed.

Eg.

Input file ----
ABCD +100.55
ABCD +055.96
ABCD +963.65
ABCD +666.33 OTHER
MNOP +050.00
MNOP -050.00
MNOP +000.00
MNOP +100.00 OTHER
PQRS +100.00
PQRS +200.00 OTHER

-----------------------------
Sum all records (except of type OTHER). If sum is zeroes, then remove those records (including OTHER). So output should be

ABCD +100.55
ABCD +055.96
ABCD +963.65
ABCD +666.33 OTHER
PQRS +100.00
PQRS +200.00 OTHER


Can someone help me on this? I know that sum can be done in one step and can be checked in another JCL step for removing the records. I would like to know any way this can be done in single step.

Thanks in advance.

Andy.
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Jan 05, 2010 11:51 pm    Post subject:
Reply with quote

You can use a DFSORT/ICETOOL job like the following to do what you asked for. I assumed your input file has RECFM=FB and LRECL=80, but the job can be changed for other attributes.

Code:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN DD DSN=...  input file (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/80)
//TOOLIN DD *
SORT FROM(IN) TO(T1) USING(CTL1)
COPY FROM(IN) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,4,CH) KEEPNODUPS KEEPBASE -
  WITHALL WITH(1,81) USING(CTL3)
/*
//CTL1CNTL DD *
  OMIT COND=(14,5,CH,EQ,C'OTHER')
  INREC BUILD=(1,4,5,8,SFF,TO=ZD,LENGTH=6,81:C'BB')
  SORT FIELDS=(1,4,CH,A)
  SUM FIELDS=(5,6,ZD)
  OUTFIL INCLUDE=(5,6,ZD,EQ,0)
/*
//CTL2CNTL DD *
  INREC OVERLAY=(81:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,INCLUDE=(81,2,CH,EQ,C'VV'),
    BUILD=(1,80)
/*
Back to top
View user's profile Send private message
Andy85

New User


Joined: 25 Dec 2008
Posts: 35
Location: India

PostPosted: Wed Jan 06, 2010 3:41 pm    Post subject:
Reply with quote

Thanks a lot Frank!!! It worked. I was facing unsuccessful sort error and so I changed the INREC BUILD under CTL1 with INREC OVERLAY. It worked fine icon_biggrin.gif

In case if I change above requirement such that those keys having zeroes in all records (except OTHER type) should be omitted, is there any chance?

So, if input is
ABCD +100.55
ABCD +055.96
ABCD +963.65
ABCD +666.33 OTHER
MNOP +000.00
MNOP +000.00
MNOP +000.00
MNOP +100.00 OTHER
PQRS +100.00
PQRS +200.00 OTHER


Output expected is:

ABCD +100.55
ABCD +055.96
ABCD +963.65
ABCD +666.33 OTHER
PQRS +100.00
PQRS +200.00 OTHER

Any modifications that would help achieve this? I am trying but all records are not coming.


Thanks again Frank.

Andy
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Jan 06, 2010 11:34 pm    Post subject:
Reply with quote

Quote:
those keys having zeroes in all records (except OTHER type) should be omitted


Quote:
If the sum is zeroes, then all the records for that key should be removed.


Huh? My job already takes care of the case where all the records have zeros. If all of the records have zeros, then the sum is zeros and the records for that key are removed. I tried my job with your example and it removes the MNOP records. Did you try it? Did you get a different result?

Quote:
I was facing unsuccessful sort error and so I changed the INREC BUILD under CTL1 with INREC OVERLAY. It worked fine


I don't know what you mean by this. What sort error? What did you change the INREC statement to? Perhaps that's why you're not getting the result I'm getting. You need to show me the error you received with my original INREC statement and what you changed it to.
Back to top
View user's profile Send private message
Andy85

New User


Joined: 25 Dec 2008
Posts: 35
Location: India

PostPosted: Thu Jan 07, 2010 10:02 am    Post subject:
Reply with quote

Yes Frank, you example do remove key records where all records under that key has zeroes. This is because sum of all these records will be zero and hence the job will take care of this. However, consider this case.

ABCD +100.55
ABCD +055.96
ABCD +666.33 OTHER
MNOP +000.00
MNOP +000.00
MNOP +000.00
MNOP +100.00 OTHER
PQRS +100.00
PQRS -100.00
PQRS +000.00 OTHER


Output expected is:

ABCD +100.55
ABCD +055.96
ABCD +963.65
ABCD +666.33 OTHER
PQRS +100.00
PQRS -100.00
PQRS +000.00 OTHER

Please note that the above job will remove MNOP and PQRS (since sum of both these records yield zeroes). However, we need to remove only those where every record is having zero value in it (like MNOP). In PQRS, both the records have non-zero (even though sum is zeroes). Hence, it should not be removed.

I hope I am able to clarify the requirement.

Thanks.

Andy
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Jan 07, 2010 10:52 pm    Post subject:
Reply with quote

Oh, I guess this is a new requirement where you want to omit records that have all zeros rather than records that sum to zero (including those that have all zeros). Here's the DFSORT/ICETOOL job for that new requirement.

Code:

//S2   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN DD DSN=...  input file (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/80)
//TOOLIN DD *
SORT FROM(IN) TO(T1) USING(CTL1)
COPY FROM(IN) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,4,CH) -
  WITHALL WITH(1,81) USING(CTL3)
/*
//CTL1CNTL DD *
  OMIT COND=(5,8,SFF,EQ,+0,OR,14,5,CH,EQ,C'OTHER')
  SORT FIELDS=(1,4,CH,A)
  SUM FIELDS=NONE
  OUTFIL OVERLAY=(81:C'BB')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(81:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,INCLUDE=(81,2,CH,EQ,C'VB'),
    BUILD=(1,80)
/*
Back to top
View user's profile Send private message
Andy85

New User


Joined: 25 Dec 2008
Posts: 35
Location: India

PostPosted: Mon Jan 11, 2010 11:46 am    Post subject:
Reply with quote

It works....superb Frank !!! icon_biggrin.gif

Thanks for the detailed help...really appreciate it.
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 Why TS/OP would Not contact site supp... RahulG31 All Other Mainframe Topics 2 Wed Mar 22, 2017 7:46 am
No new posts Remove junk values in a file and rite... KP1125 DFSORT/ICETOOL 2 Wed Jan 25, 2017 9:58 pm
No new posts Sort Card to Remove Duplicate records... raj4neo SYNCSORT 2 Wed Jan 25, 2017 4:44 am
No new posts Sorting group data rajella DFSORT/ICETOOL 4 Sun Jan 22, 2017 11:32 pm
No new posts Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am


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