Select MAX value from multiple fields in a record using SORT



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Select MAX value from multiple fields in a record using SORT

Postby pvprasadrao12 » Fri May 23, 2008 4:41 am

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
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

Postby 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
User avatar
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

Postby 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
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

Postby 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


//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)                                                 
/*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Select MAX value from multiple fields in a record using SORT

Postby pvprasadrao12 » Wed May 28, 2008 12:19 am

Hi,

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


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post