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
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
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.
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
I had a similar scenario, with many fields involved. So i thought i will simplify it here! Thanks Bill.
