|
View previous topic :: View next topic
|
| Author |
Message |
hellosri
New User
Joined: 14 May 2007
Posts: 7
Location: CT, USA
|
|
|
|
Hello Team,
I have an input file as follows.
KEY(Pos 1-8) %AMOUNT (Pos 10-16)
TABLE1 88.92
TABLE2 87.00
TABLE1 86.70
TABLE1 86.00
TABLE3 85.59
TABLE4 85.00
TABLE2 84.92
TABLE1 80.00
TABLE6 90.00
I want to SORT the file first based on highest% value and for every highest % value of a table all other records for that table should be sorted in the order.
Output expected
TABLE6 90.00
TABLE1 88.92
TABLE1 86.70
TABLE1 86.00
TABLE1 80.00
TABLE2 87.00
TABLE2 84.92
TABLE3 85.59
TABLE4 85.00
I have tried several options but not able to find a way to do this.
Option-1 I tried
//SYSIN DD *
SORT FIELDS=(10,6,CH,D)
OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,8),
PUSH=(1:1,20))
/*
Option-2 I tried
SORT FIELDS=(1,8,CH,A,10,6,CH,D)
OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,8),
PUSH=(1:1,20))
Option-3 I tried
//SYSIN DD *
SORT FIELDS=(10,6,ZD,A,1,8,CH,A)
OUTREC FIELDS=(1,20)
/*
I have been searching all the forums and have not had any luck so far. Any help would be greatly appreciated. |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2288
Location: USA
|
|
|
|
Example #1 from ANY(!) Sort utility manual.
| Code: |
SORT FIELDS=(1,8,CH,A,
10,16,SFF,D)
END |
| Code: |
SORT FIELDS=(10,16,SFF,D,
1,8,CH,A)
END |
Format CH may not always work for numeric data, but sometimes it does.
P.S.
The general idea of your desired output is not fully clear… |
|
| Back to top |
|
|
hellosri
New User
Joined: 14 May 2007
Posts: 7
Location: CT, USA
|
|
|
|
Thank you for your suggesstion but it didnt work and I apologize for not being more cleared. Let me try one more time to see if you can understand.
KEY(Pos 1-8) %AMOUNT (Pos 10-16)
TABLE1 88.92 -> First highest value for TABLE1, but this is second highest value of all records.
TABLE2 87.00 -> First highest value for TABLE2, but this is the third highest value of all records.
TABLE1 86.70
TABLE1 86.00
TABLE3 85.59 -> First highest value for TABLE3
TABLE4 85.00 -> First highest value for TABLE4
TABLE2 84.92
TABLE1 80.00
TABLE6 90.00 -> First highest value for TABLE6, but this is the first highest value of all records.
The output file should have TABLE6 first because 90.00 is the highest % of all the records. And if there are more records for the same TABLE6 it should be followed. In the above example there is only one TABLE6. This completes the first highest % row written and all the records associated for that TABLE6. The next highest will be for TABLE1 88.92 and all the records for TABLE1 should be sorted in the descending order of the % and so on for other tables also. I know its little complicated but my final output should be first sorted based on % value in descending order and write all the records for that associated table and go on. Let me know if this helps.
Output expected
TABLE6 90.00
TABLE1 88.92
TABLE1 86.70
TABLE1 86.00
TABLE1 80.00
TABLE2 87.00
TABLE2 84.92
TABLE3 85.59
TABLE4 85.00 |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1443
Location: Bamberg, Germany
|
|
|
|
| I am afraid you have to take multiple passes to achieve what you want. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2288
Location: USA
|
|
|
|
Those are two different goals:
1) get one single line with the highest numeric value (whatever the value means)
2) re-order all rows of the table in the required order
It is just senseless idea to mix those two different goals, different methods, different results, and different statements into one single task/code/resulting table.
They are two completely separate tasks, and two completely different output results
Try to perform those two tasks separately, and do not mix them into one messy task.
THEY ARE COMPLETELY DIFFERENT, do have nothing in common (except the same input data)
P.S.
Again, as in >90% of all the questions at this forum, the issue is not a specific programming tool, or any language syntax, or an internal function, but just inability of abstract logical thinking in general, and lack of experience in algorithm design |
|
| Back to top |
|
|
hellosri
New User
Joined: 14 May 2007
Posts: 7
Location: CT, USA
|
|
|
|
Thanks, it is okay for people to think and try to come up to achieve solutions in a single step. Atleast I tried and that is why I requested help to see if there were any feasible solution. Thank you for reviewing and trying though.
Cheers Mate! |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1443
Location: Bamberg, Germany
|
|
|
|
| Code: |
//PREPARE EXEC PGM=ICEMAN
//SORTIN DD *
TABLE1 88.92
TABLE2 87.00
TABLE1 86.70
TABLE1 86.00
TABLE3 85.59
TABLE4 85.00
TABLE2 84.92
TABLE1 80.00
TABLE6 90.00
/*
//SYSOUT DD SYSOUT=*
//F1 DD DISP=(NEW,PASS),UNIT=SYSALLDA,SPACE=(CYL,(2,1),RLSE)
//F2 DD DISP=(NEW,PASS),UNIT=SYSALLDA,SPACE=(CYL,(2,1),RLSE)
//SORTOUT DD DUMMY
//SYSIN DD *
SORT FIELDS=(1,6,CH,A,8,5,SFF,D)
OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(20:SEQNUM,4,ZD,RESTART=(1,6)))
OUTFIL FNAMES=(F1),
REMOVECC
OUTFIL FNAMES=(F2),
INCLUDE=(20,4,ZD,EQ,+1),
REMOVECC,
BUILD=(1,12)
END
/*
//WHATEVER EXEC PGM=ICEMAN
//F1 DD DISP=(OLD,PASS),DSN=*.PREPARE.F1
//F2 DD DISP=(OLD,PASS),DSN=*.PREPARE.F2
//SYSOUT DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS F1=F1,FIELDS=(1,6,D)
JOINKEYS F2=F2,FIELDS=(1,6,D)
REFORMAT FIELDS=(F1:1,24,F2:8,5)
SORT FIELDS=(25,5,SFF,D,1,6,CH,A,20,4,ZD,A)
OUTFIL FNAMES=(SORTOUT),
REMOVECC,
BUILD=(1,12)
END
/* |
Output:
| Code: |
****** *****************
000001 TABLE6 90.00
000002 TABLE1 88.92
000003 TABLE1 86.70
000004 TABLE1 86.00
000005 TABLE1 80.00
000006 TABLE2 87.00
000007 TABLE2 84.92
000008 TABLE3 85.59
000009 TABLE4 85.00
****** ***************** |
|
|
| Back to top |
|
|
hellosri
New User
Joined: 14 May 2007
Posts: 7
Location: CT, USA
|
|
|
|
Joerg, Thank you for your response. I am out of office next week. But I will try your code as soon as I come back and will respond.
Thank you for taking time to help me achieve with the solution although it is in two steps.
Thanks
Sri |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2288
Location: USA
|
|
|
|
| Using ICETOOL/SYNCTOOL you can “pack” these two JCL steps into two passes within a single JCL EXEC PGM |
|
| Back to top |
|
|
hellosri
New User
Joined: 14 May 2007
Posts: 7
Location: CT, USA
|
|
|
|
| Joerg - I tried out the solution you gave and it worked as expected. Thank you once again for helping out and for your time. Very much appreciated. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
