|
View previous topic :: View next topic
|
| Author |
Message |
afroz.alam
New User
Joined: 15 Mar 2006
Posts: 4
|
|
|
|
I have a flat file like shown below:
| Code: |
Item_Desc Order_ID Quantity
--------------------------------------------------------------
Sofa Or1 75
Sofa Or2 100
Chair Or3 90
Chair Or4 100
Table Or5 67
Sheet Or6 78
Blanket Or7 90
Pillow Or8 60
Pillow Or9 100
|
I want to create an output file from this input file where the rows need to be sorted after computing the average in case of Item_Desc occuring more than once. For items occuring only once, average quantity = quantity.
Basically, I want output for this case as follows :
| Code: |
Item Desc Order_ID Quantity
-----------------------------------------------------------
Chair Or3 80
Chair Or4 100
Blanket Or7 90
Pillow Or8 60
Pillow Or9 100
Sheet Or6 78
Sofa Or1 75
Sofa Or2 100
Table Or5 67 |
Please suggest a solution for this problem using either ICETOOL or SORT.
Thanks |
|
| Back to top |
|
 |
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
| After 5 years of reading the forum you still do not use code tags or provide the basic information asked for in almost every sort topic. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10900
Location: italy
|
|
|
|
I edited the post for the code tags, but H311 will freeze before I waste time reformatting  |
|
| Back to top |
|
|
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
AVG
specifies that an edited or converted average, for the values of a numeric input field in all data records of the report, is to appear in the report record. The average (or mean) is calculated by dividing the total by the count and rounding down to the nearest integer. For example:
10:'Average revenue: ',AVG=(25,5,PD,M5)) |
|
| Back to top |
|
|
afroz.alam
New User
Joined: 15 Mar 2006
Posts: 4
|
|
|
|
I apologize for the clumsy representation of information.
However, gylbharat, can you please help me with a complete control card of the AVG computation you mentioned below. Please let me know if you need more information on the problem I posted. |
|
| Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
afroz.alam,
Your output does not match the specifications. You say you wanted to sort the records by descending average. so how did the PILLOW with an average of 80 (100+60 =160/2 =80) get sorted before SOFA with an average of 87.5 (75+100 =175/2 = 87.5) ?? I am guessing that they are typos. Here is DFSORT JCL which will give you the desired results.
Assumptions :
LRECL = 80
RECFM = FB
Item_Desc = 15 bytes starts at pos 1
Order_ID = 10 bytes starts at pos 16
Quantity = 05 bytes starts at pos 26 with leading zeroes supressed.
Use the same input file for both DD's INA and INB.
| Code: |
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//INA DD *
----+----1----+----2----+----3----+
SOFA OR1 75
SOFA OR2 100
CHAIR OR3 90
CHAIR OR4 100
TABLE OR5 67
SHEET OR6 78
BLANKET OR7 90
PILLOW OR8 60
PILLOW OR9 100
//INB DD *
SOFA OR1 75
SOFA OR2 100
CHAIR OR3 90
CHAIR OR4 100
TABLE OR5 67
SHEET OR6 78
BLANKET OR7 90
PILLOW OR8 60
PILLOW OR9 100
//SORTOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS F1=INA,FIELDS=(01,15,A)
JOINKEYS F2=INB,FIELDS=(01,15,A)
REFORMAT FIELDS=(F1:1,80,F2:16,9)
INREC OVERLAY=(91:(81,5,ZD,MUL,+100),DIV,87,3,ZD,EDIT=(TTTTT.TT))
SORT FIELDS=(91,8,CH,D,1,15,CH,A),EQUALS
OUTREC BUILD=(1,80)
//*
//JNF2CNTL DD *
INREC BUILD=(1,15,26,5,UFF,M11,LENGTH=5,C' 001')
SUM FIELDS=(16,5,22,3),FORMAT=ZD
//* |
The output from this is
| Code: |
----+----1----+----2----+----3----+
CHAIR OR3 90
CHAIR OR4 100
BLANKET OR7 90
SOFA OR1 75
SOFA OR2 100
PILLOW OR8 60
PILLOW OR9 100
SHEET OR6 78
TABLE OR5 67
|
|
|
| Back to top |
|
|
afroz.alam
New User
Joined: 15 Mar 2006
Posts: 4
|
|
|
|
Hi Skolusu,
When I copy your code and try to run at my shop i am getting an error -
Am i missing anything?
| Code: |
000004 //STEP0100 EXEC PGM=SORT,PARM='NOLIST'
000005 //SYSOUT DD SYSOUT=*
000006 //INA DD *
000007 SOFA OR1 75
000008 SOFA OR2 100
000009 CHAIR OR3 90
000010 CHAIR OR4 100
000011 TABLE OR5 67
000012 SHEET OR6 78
000013 BLANKET OR7 90
000014 PILLOW OR8 60
000015 PILLOW OR9 100
000016 //INB DD *
000017 SOFA OR1 75
000018 SOFA OR2 100
000019 CHAIR OR3 90
000020 CHAIR OR4 100
000021 TABLE OR5 67
000022 SHEET OR6 78
000023 BLANKET OR7 90
000024 PILLOW OR8 60
000025 PILLOW OR9 100
000026 //SORTOUT DD SYSOUT=*
000027 //SYSIN DD *
000028 JOINKEYS F1=INA,FIELDS=(01,15,A)
000029 JOINKEYS F2=INB,FIELDS=(01,15,A)
000030 REFORMAT FIELDS=(F1:1,80,F2:16,9)
000031 INREC OVERLAY=(91:(81,5,ZD,MUL,+100),DIV,87,3,ZD,EDIT=(TTTTT.TT))
000032 SORT FIELDS=(91,8,CH,D,1,15,CH,A),EQUALS
000033 OUTREC BUILD=(1,80)
000034 //JNF2CNTL DD *
000035 INREC BUILD=(1,15,26,5,UFF,M11,LENGTH=5,C' 001')
000036 SUM FIELDS=(16,5,22,3),FORMAT=ZD
000037 //*
==========================
Error Msg:
WER161B ALTERNATE PARM USED
WER268A JOINKEYS STATEMENT: SYNTAX ERROR
WER268A JOINKEYS STATEMENT: SYNTAX ERROR
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
******************************* Bottom of Data ********** |
|
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 767
Location: Whitby, ON, Canada
|
|
|
|
| You are using SYNCSORT not DFSORT. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
You need to post the page(s) with the diagnostic informaton - from the top to the bottom. |
|
| Back to top |
|
|
afroz.alam
New User
Joined: 15 Mar 2006
Posts: 4
|
|
|
|
| Quote: |
| You are using SYNCSORT not DFSORT. |
I am not sure if our shop has or supports DFSORT. Can you please provide control card supported by SYNCSORT? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6965
Location: porcelain throne
|
|
|
|
there was a thread just the other day, where someone was using syncsort with joinkeys. syncsort has other syntax or can't use jfn1cntl or refomat or something.
i use dfsort so didnot bother to remember,
but, looking thru posts for the last couple of days will provide the answer,
since the ts obviously does not feel compelled to consult a manual. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
Here's a hint. . . Getting late here and i've one quite similar. . .
JOINKEYS FILE=F1,FIELDS=(01,15,A)
JOINKEYS FILE=F2,FIELDS=(01,15,A) |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
