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
 

 

Joinkeys to Groupby and sum up

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

Active User


Joined: 14 Dec 2008
Posts: 103
Location: India

PostPosted: Thu Oct 22, 2015 1:55 am    Post subject: Joinkeys to Groupby and sum up
Reply with quote

Hi,

I have Employee_No, City, Type and two amount fields in the input file as shown below. I want to sum the amount fields and do a group by on Emp_No and Type both as shown in the output.

Input
Code:

100 Chicago Food 010 003
100 NYC     Fuel 015 004
100 Chicago Food 020 005
200 Phoenix Food 015 006
200 Atlanta Food 020 001


Output
Code:

100 Food 030 008
100 Fuel 015 004
200 Food 035 007


My code:
Code:
//INPUTA DD DSN=..File-1
//INPUTB DD DSN=..File-1                                               
//F1OUT DD SYSOUT=*                                       
//SYSIN   DD    *                                         
  JOINKEYS F1=INPUTA,FIELDS=(1,3,A,13,4,A)                 
  JOINKEYS F2=INPUTB,FIELDS=(1,3,A,13,4,A)                 
  JOIN UNPAIRED,F1,F2                                     
  REFORMAT FIELDS=(F2:1,4,F2:13,12)               
  OPTION COPY                                             
  OUTFIL FNAMES=F1ONLY,BUILD=(1,80)                     
  END                                                     
/*                                                       
//JNF1CNTL DD *                   
  INREC OVERLAY=(81:SEQNUM,8,ZD)
/*                               
//JNF2CNTL DD *                   
  SUM FIELDS=(18,03,ZD,22,03,ZD)
/*


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

RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Thu Oct 22, 2015 4:04 am    Post subject: Reply to: Joinkeys to Groupby and sum up
Reply with quote

I am not sure why you are doing a joinkeys. You could get your desired output by:
Code:
 SORT FIELDS=(1,3,CH,A,13,4,CH,A)
 SUM FIELDS=(18,3,ZD,22,3,ZD)
 OUTREC FIELDS=(1,4,13,14)

I assumed input length as 26 bytes and that is why 13,14 in outrec
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Thu Oct 22, 2015 4:41 am    Post subject: Reply to: Joinkeys to Groupby and sum up
Reply with quote

I don't see a need for JOINKEYS either.

You have data on your input that you don't need on your output. So cut it down with INREC, to reduce the amount of data SORTed.

Because you have to SORT, the SUM is reasonable. You could also consider OUTFIL reporting features, REMOVECC, NODETAIL, SECTIONS with TRAILER3. An advantage of this approach is that you don't have to wonder whether you are going to bust your counts with SUM (if so, to continue to use SUM, you have to define extra space in INREC).

Although you could get the JOINKEYS to work for this task, it is extra resources in the use of JOINKEYS itself, requires two SORTs (implicit in the JOINKEYS) and, probably the problem you have (you haven't said) two SUMs (or another way to stop multiple outputs).

You're trying to use JOINKEYS as "SQL", and that on the basis that SQL can solve everything, and not be concerned with efficiency.

Is this a for a class, or a competition?
Back to top
View user's profile Send private message
hiravibk
Warnings : 1

Active User


Joined: 14 Dec 2008
Posts: 103
Location: India

PostPosted: Fri Oct 23, 2015 12:33 am    Post subject: Re: Reply to: Joinkeys to Groupby and sum up
Reply with quote

RahulG31 wrote:
I am not sure why you are doing a joinkeys. You could get your desired output by:
Code:
 SORT FIELDS=(1,3,CH,A,13,4,CH,A)
 SUM FIELDS=(18,3,ZD,22,3,ZD)
 OUTREC FIELDS=(1,4,13,14)

I assumed input length as 26 bytes and that is why 13,14 in outrec


Thank you so much RahulG31. I was complicating things using Joinkeys.

@Bill: Thats right, i will include INREC to cut down the data. This isn't for a class or competition icon_smile.gif
I had a similar scenario, with many fields involved. So i thought i will simplify it here! Thanks Bill.
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 Joinkeys operation when key1 and key2... Susanta DFSORT/ICETOOL 5 Wed Nov 02, 2016 5:35 pm
No new posts Joinkeys operation betwen VB and FB file Susanta SYNCSORT 4 Sun Sep 25, 2016 9:49 pm
No new posts Joinkeys with duplicated keys juares castro SYNCSORT 19 Fri Sep 23, 2016 5:58 am
No new posts Icetool or Joinkeys example to compar... ishant chauhan DFSORT/ICETOOL 21 Sat Aug 20, 2016 2:40 am
No new posts JOINKEYs and Variable files migusd SYNCSORT 8 Fri Jun 17, 2016 3:16 am


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