Hi,
I have 30 fields in a record Each field can have number ranging from 001 to 100.
The 30 fields are divided into 3 categories like first 10 fields are in A group, Second 10 are in B group, Third 10 fields are in C group.
I have to identify the max value of these 30 fields and the group associated to this max value
So my out put will have 30 fields and Max Value of 30 fields(000 to 100) and Group(ONE LETTER OR NUMBER LIKE A,B,C OR 1,2,3)
I request help to find the simplest way to do by sort technique.
I have around 3 lakh records in the input file which needs to identify max value and the group done for all the records
KIndly let me know if more information is required
thanks
Venkata Perumala
Select MAX value from multiple fields in a record using SORT
Previous topic • Next topic • 5 posts
• Page 1 of 1
Select MAX value from multiple fields in a record using SORT
by pvprasadrao12 » Fri May 23, 2008 4:41 am
- pvprasadrao12
- Posts: 3
- Joined: Fri May 23, 2008 4:28 am
- Has thanked: 0 time
- Been thanked: 0 time
Re: Select MAX value from multiple fields in a record using SORT
by Frank Yaeger » Fri May 23, 2008 5:54 am
Show an example of the records in the input file (relevant fields only) and the expected output records. Give the RECFM and LRECL of the input file. Give the starting position, length and format of each relevant field.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
-
Frank Yaeger - Global moderator
- Posts: 1079
- Joined: Sat Jun 09, 2007 8:44 pm
- Has thanked: 0 time
- Been thanked: 15 times
Re: Select MAX value from multiple fields in a record using SORT
by pvprasadrao12 » Fri May 23, 2008 9:45 pm
Hi,
I am posting my actual Problem here. Please cosider the MINimum score
It helps me a lot if quick resolution is provided or posted.
Please ignore my previous post which I generalised the problem and posted.
The record lenght is 1500. LRECL = 1500
The record format is FB RECFM = FB
Input file REC length: 1500
Output file Rec lenght: 1500
I have the scores for following fields ranges from values (000 to 100) is of lenght 3
I want to find the minimum score from all these fields and need to identify from which group the minimum score came.
The minimum score has to be kept at pos 1490-1492
The group having minimum score is kept at pos 1495 as 'D' or 'C' or 'J'
The below fields are from DODGE group 'D'
q_magnum starts at pos 1091 has score 001
q_caravan_gcaravan starts at pos 1100 has score 011
q_durango starts at pos 1103 has score 021
q_dakota starts at pos 1106 has score 001
q_ram1500 starts at pos 1145 has score 001
q_ramheavy starts at pos 1148 has score 041
q_charger starts at pos 1094 has score 001
q_nitro starts at pos 1133 has score 082
q_avenger starts at pos 1151 has score 001
q_caliber starts at pos 1130 has score 093
q_journey starts at pos 1154 has score 100
The below fields are from CHRYSLER group 'C'
q_pacifica starts at pos 1082 has score 004
q_ptcrusr starts at pos 1073 has score 002
q_sebring starts at pos 1076 has score 001
q_townctry starts at pos 1085 has score 001
q_chrys_300 starts at pos 1079 has score 001
q_aspen starts at pos 1142 has score 022
The below fields are from JEEP group 'J'
q_wrangler starts at pos 1109 has score 014
q_liberty starts at pos 1112 has score 035
q_gcherokee starts at pos 1115 has score 001
q_commander starts at pos 1127 has score 033
q_compass starts at pos 1136 has score 043
q_patroit starts at pos 1139 has score 094
The minimum score is 001 (This min score exists in Dodge, Chrysler and Jeep)
So I have in the output file
at POS 1490 - 001 (of lenght 3 char or number)
and at POS 1495 as 'DCJ' (of 3 char lenght)
If min score exists from only one group then at POS1495 I will be having 'D '
POS 1495 can have 3 character fields
Kindly let me know if any more information needed.
Thanks & Regards
Venkata Perumala
I am posting my actual Problem here. Please cosider the MINimum score
It helps me a lot if quick resolution is provided or posted.
Please ignore my previous post which I generalised the problem and posted.
The record lenght is 1500. LRECL = 1500
The record format is FB RECFM = FB
Input file REC length: 1500
Output file Rec lenght: 1500
I have the scores for following fields ranges from values (000 to 100) is of lenght 3
I want to find the minimum score from all these fields and need to identify from which group the minimum score came.
The minimum score has to be kept at pos 1490-1492
The group having minimum score is kept at pos 1495 as 'D' or 'C' or 'J'
The below fields are from DODGE group 'D'
q_magnum starts at pos 1091 has score 001
q_caravan_gcaravan starts at pos 1100 has score 011
q_durango starts at pos 1103 has score 021
q_dakota starts at pos 1106 has score 001
q_ram1500 starts at pos 1145 has score 001
q_ramheavy starts at pos 1148 has score 041
q_charger starts at pos 1094 has score 001
q_nitro starts at pos 1133 has score 082
q_avenger starts at pos 1151 has score 001
q_caliber starts at pos 1130 has score 093
q_journey starts at pos 1154 has score 100
The below fields are from CHRYSLER group 'C'
q_pacifica starts at pos 1082 has score 004
q_ptcrusr starts at pos 1073 has score 002
q_sebring starts at pos 1076 has score 001
q_townctry starts at pos 1085 has score 001
q_chrys_300 starts at pos 1079 has score 001
q_aspen starts at pos 1142 has score 022
The below fields are from JEEP group 'J'
q_wrangler starts at pos 1109 has score 014
q_liberty starts at pos 1112 has score 035
q_gcherokee starts at pos 1115 has score 001
q_commander starts at pos 1127 has score 033
q_compass starts at pos 1136 has score 043
q_patroit starts at pos 1139 has score 094
The minimum score is 001 (This min score exists in Dodge, Chrysler and Jeep)
So I have in the output file
at POS 1490 - 001 (of lenght 3 char or number)
and at POS 1495 as 'DCJ' (of 3 char lenght)
If min score exists from only one group then at POS1495 I will be having 'D '
POS 1495 can have 3 character fields
Kindly let me know if any more information needed.
Thanks & Regards
Venkata Perumala
- pvprasadrao12
- Posts: 3
- Joined: Fri May 23, 2008 4:28 am
- Has thanked: 0 time
- Been thanked: 0 time
Re: Select MAX value from multiple fields in a record using SORT
by skolusu » Sat May 24, 2008 12:06 am
The following DFSORT JCL will give you the desired results. we first populate 1 value from each group at the end of every record. For chyrsler the start position to be compared is at position 1073, for Dodge the start position to be compared is at pos 1091 and for Jeep the start position to be compared is at pos 1109.
Once we populated those initial values now we check the other postions for the same group and in the end compare the 3 values at the end and populate it accordingly
Once we populated those initial values now we check the other postions for the same group and in the end compare the 3 values at the end and populate it accordingly
//STEP0100 EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=your input file,IN,DISP=SHR
//SORTOUT DD DSN=Your output file,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(X,Y0,RLSE)
//SYSIN DD *
SORT FIELDS=COPY
INREC IFTHEN=(WHEN=INIT,OVERLAY=(1501:1073,3,1091,3,1109,3)),
IFTHEN=(WHEN=(1073,3,ZD,LE,1501,3,ZD),OVERLAY=(1501:1073,3),HIT=NEXT),
IFTHEN=(WHEN=(1076,3,ZD,LE,1501,3,ZD),OVERLAY=(1501:1076,3),HIT=NEXT),
IFTHEN=(WHEN=(1079,3,ZD,LE,1501,3,ZD),OVERLAY=(1501:1079,3),HIT=NEXT),
IFTHEN=(WHEN=(1082,3,ZD,LE,1501,3,ZD),OVERLAY=(1501:1082,3),HIT=NEXT),
IFTHEN=(WHEN=(1085,3,ZD,LE,1501,3,ZD),OVERLAY=(1501:1085,3),HIT=NEXT),
IFTHEN=(WHEN=(1142,3,ZD,LE,1501,3,ZD),OVERLAY=(1501:1142,3),HIT=NEXT),
IFTHEN=(WHEN=(1091,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1091,3),HIT=NEXT),
IFTHEN=(WHEN=(1094,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1094,3),HIT=NEXT),
IFTHEN=(WHEN=(1100,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1100,3),HIT=NEXT),
IFTHEN=(WHEN=(1103,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1103,3),HIT=NEXT),
IFTHEN=(WHEN=(1106,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1106,3),HIT=NEXT),
IFTHEN=(WHEN=(1130,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1130,3),HIT=NEXT),
IFTHEN=(WHEN=(1133,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1133,3),HIT=NEXT),
IFTHEN=(WHEN=(1145,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1145,3),HIT=NEXT),
IFTHEN=(WHEN=(1148,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1148,3),HIT=NEXT),
IFTHEN=(WHEN=(1151,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1151,3),HIT=NEXT),
IFTHEN=(WHEN=(1154,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1154,3),HIT=NEXT),
IFTHEN=(WHEN=(1109,3,ZD,LE,1507,3,ZD),OVERLAY=(1507:1109,3),HIT=NEXT),
IFTHEN=(WHEN=(1112,3,ZD,LE,1507,3,ZD),OVERLAY=(1507:1112,3),HIT=NEXT),
IFTHEN=(WHEN=(1115,3,ZD,LE,1507,3,ZD),OVERLAY=(1507:1115,3),HIT=NEXT),
IFTHEN=(WHEN=(1127,3,ZD,LE,1507,3,ZD),OVERLAY=(1507:1127,3),HIT=NEXT),
IFTHEN=(WHEN=(1136,3,ZD,LE,1507,3,ZD),OVERLAY=(1507:1136,3),HIT=NEXT),
IFTHEN=(WHEN=(1139,3,ZD,LE,1507,3,ZD),OVERLAY=(1507:1139,3),HIT=NEXT),
IFTHEN=(WHEN=((1501,3,ZD,EQ,1504,3,ZD),AND,(1501,3,ZD,EQ,1507,3,ZD)),
OVERLAY=(1490:1501,3,1495:C'DCJ'),HIT=NEXT),
IFTHEN=(WHEN=((1501,3,ZD,LT,1504,3,ZD),AND,(1501,3,ZD,LT,1507,3,ZD)),
OVERLAY=(1490:1501,3,1495:C' C '),HIT=NEXT),
IFTHEN=(WHEN=((1504,3,ZD,LT,1501,3,ZD),AND,(1504,3,ZD,LT,1507,3,ZD)),
OVERLAY=(1490:1504,3,1495:C'D '),HIT=NEXT),
IFTHEN=(WHEN=((1507,3,ZD,LT,1501,3,ZD),AND,(1507,3,ZD,LT,1504,3,ZD)),
OVERLAY=(1490:1507,3,1495:C' J'))
OUTREC BUILD=(1,1500)
/*
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=your input file,IN,DISP=SHR
//SORTOUT DD DSN=Your output file,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(X,Y0,RLSE)
//SYSIN DD *
SORT FIELDS=COPY
INREC IFTHEN=(WHEN=INIT,OVERLAY=(1501:1073,3,1091,3,1109,3)),
IFTHEN=(WHEN=(1073,3,ZD,LE,1501,3,ZD),OVERLAY=(1501:1073,3),HIT=NEXT),
IFTHEN=(WHEN=(1076,3,ZD,LE,1501,3,ZD),OVERLAY=(1501:1076,3),HIT=NEXT),
IFTHEN=(WHEN=(1079,3,ZD,LE,1501,3,ZD),OVERLAY=(1501:1079,3),HIT=NEXT),
IFTHEN=(WHEN=(1082,3,ZD,LE,1501,3,ZD),OVERLAY=(1501:1082,3),HIT=NEXT),
IFTHEN=(WHEN=(1085,3,ZD,LE,1501,3,ZD),OVERLAY=(1501:1085,3),HIT=NEXT),
IFTHEN=(WHEN=(1142,3,ZD,LE,1501,3,ZD),OVERLAY=(1501:1142,3),HIT=NEXT),
IFTHEN=(WHEN=(1091,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1091,3),HIT=NEXT),
IFTHEN=(WHEN=(1094,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1094,3),HIT=NEXT),
IFTHEN=(WHEN=(1100,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1100,3),HIT=NEXT),
IFTHEN=(WHEN=(1103,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1103,3),HIT=NEXT),
IFTHEN=(WHEN=(1106,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1106,3),HIT=NEXT),
IFTHEN=(WHEN=(1130,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1130,3),HIT=NEXT),
IFTHEN=(WHEN=(1133,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1133,3),HIT=NEXT),
IFTHEN=(WHEN=(1145,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1145,3),HIT=NEXT),
IFTHEN=(WHEN=(1148,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1148,3),HIT=NEXT),
IFTHEN=(WHEN=(1151,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1151,3),HIT=NEXT),
IFTHEN=(WHEN=(1154,3,ZD,LE,1504,3,ZD),OVERLAY=(1504:1154,3),HIT=NEXT),
IFTHEN=(WHEN=(1109,3,ZD,LE,1507,3,ZD),OVERLAY=(1507:1109,3),HIT=NEXT),
IFTHEN=(WHEN=(1112,3,ZD,LE,1507,3,ZD),OVERLAY=(1507:1112,3),HIT=NEXT),
IFTHEN=(WHEN=(1115,3,ZD,LE,1507,3,ZD),OVERLAY=(1507:1115,3),HIT=NEXT),
IFTHEN=(WHEN=(1127,3,ZD,LE,1507,3,ZD),OVERLAY=(1507:1127,3),HIT=NEXT),
IFTHEN=(WHEN=(1136,3,ZD,LE,1507,3,ZD),OVERLAY=(1507:1136,3),HIT=NEXT),
IFTHEN=(WHEN=(1139,3,ZD,LE,1507,3,ZD),OVERLAY=(1507:1139,3),HIT=NEXT),
IFTHEN=(WHEN=((1501,3,ZD,EQ,1504,3,ZD),AND,(1501,3,ZD,EQ,1507,3,ZD)),
OVERLAY=(1490:1501,3,1495:C'DCJ'),HIT=NEXT),
IFTHEN=(WHEN=((1501,3,ZD,LT,1504,3,ZD),AND,(1501,3,ZD,LT,1507,3,ZD)),
OVERLAY=(1490:1501,3,1495:C' C '),HIT=NEXT),
IFTHEN=(WHEN=((1504,3,ZD,LT,1501,3,ZD),AND,(1504,3,ZD,LT,1507,3,ZD)),
OVERLAY=(1490:1504,3,1495:C'D '),HIT=NEXT),
IFTHEN=(WHEN=((1507,3,ZD,LT,1501,3,ZD),AND,(1507,3,ZD,LT,1504,3,ZD)),
OVERLAY=(1490:1507,3,1495:C' J'))
OUTREC BUILD=(1,1500)
/*
Re: Select MAX value from multiple fields in a record using SORT
by pvprasadrao12 » Wed May 28, 2008 12:19 am
Hi,
Thanks a lot for quick reply.
Thanks
Venkata Perumala
Thanks a lot for quick reply.
Thanks
Venkata Perumala
- pvprasadrao12
- Posts: 3
- Joined: Fri May 23, 2008 4:28 am
- Has thanked: 0 time
- Been thanked: 0 time
Previous topic • Next topic • 5 posts
• Page 1 of 1
Return to DFSORT/ICETOOL/ICEGENER
-
- Related topics
- Replies
- Views
- Last post
-
- Sort fields= none, Last record of each group
by kiranmayipv » Wed Sep 22, 2010 6:38 pm - 1 Replies
- 3997 Views
- Last post by skolusu
Wed Sep 22, 2010 9:33 pm
- Sort fields= none, Last record of each group
-
- Select next record based on previous record
by Ron Mascarenhas » Wed Dec 31, 2008 4:14 pm - 1 Replies
- 5403 Views
- Last post by Frank Yaeger
Wed Dec 31, 2008 9:56 pm
- Select next record based on previous record
-
- Combine multiple record on key to single record
by mfnme » Wed Nov 06, 2013 5:17 pm - 2 Replies
- 3215 Views
- Last post by BillyBoyo
Wed Nov 06, 2013 6:46 pm
- Combine multiple record on key to single record
-
- Sum of same fields in same record
by pgkkreddy1 » Mon Jun 01, 2015 10:40 pm - 7 Replies
- 2860 Views
- Last post by BillyBoyo
Wed Jun 03, 2015 12:51 pm
- Sum of same fields in same record
-
- Summing 2 fields of same record into 1
by D9988 » Tue Mar 22, 2011 12:27 am - 2 Replies
- 8527 Views
- Last post by D9988
Wed Mar 23, 2011 1:33 am
- Summing 2 fields of same record into 1