Displaying Largest value in a field and removing duplicates



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

Displaying Largest value in a field and removing duplicates

Postby Sanjana » Mon Jan 09, 2017 7:24 pm

Hi
I have an input file with data like below:
00000009NXP00600
00000010NXP00420
00000011NXP00562
00000011NXP00572
00000011NXP00672
00000012NXP00111
 

My output should contain 16 bytes. There should be no duplicates. If you see there are 3 entries where the first 8 bytes are 00000011,I want an output like below:
00000009NXP00600E
00000010NXP00420E
00000011NXP00672E
00000012NxP00111E

As in only the last 3 bytes which are highest in value are displayed. Here,672.
//SYSIN DD *                                            
      SORT FIELDS=(1,10,CH,A)                            
      SUM FIELDS=NONE                                    
      INCLUDE COND=(9,3,CH,EQ,C'NXP')                    
      OUTREC FIELDS=(1:1,8,9:9,8)                
      OUTFIL REMOVECC,                                  
            HEADER1=(1:'NXP ENTITLEMENT BFES'),          
            TRAILER1=('BFE COUNT:',COUNT=(M1,LENGTH=3))  
/*                                                      



this code gives me the output as below:
00000009NXP00600
00000010NXP00420
00000011NXP00562
00000012NxP00111E

It is displaying the lowest number 562.
could someone tell me how to do it? Thanks!
Sanjana
 
Posts: 11
Joined: Sat Dec 10, 2016 11:42 am
Has thanked: 0 time
Been thanked: 0 time

Re: Displaying Largest value in a field and removing duplica

Postby Akatsukami » Mon Jan 09, 2017 8:51 pm

This is not JCL, but *Sort. As you are careful not to let us know which sort, I will start by moving this to the DFSORT forum.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: Displaying Largest value in a field and removing duplica

Postby BillyBoyo » Mon Jan 09, 2017 11:18 pm

Since you show in your sample that your data is already in sequence, why SORT it?

You've used SORT because you want to use SUM. But SUM is going to use the first record that it encounters as the base.

Instead, use OUTFIL reporting features, REMOVECC, NODETAIL, and SECTIONS with TRAILER3.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Displaying Largest value in a field and removing duplica

Postby Sanjana » Wed Jan 11, 2017 11:12 am

Hi,
The data in my input file is not in a sorted order, I only gave a sample. May I use sum if the sort fields are like below?
SORT FIELDS=(1,8,CH,A,9,8,CH,D)
Sanjana
 
Posts: 11
Joined: Sat Dec 10, 2016 11:42 am
Has thanked: 0 time
Been thanked: 0 time

Re: Displaying Largest value in a field and removing duplica

Postby BillyBoyo » Wed Jan 11, 2017 12:03 pm

If your data is not in sequence, then please don't show sample data as in-sequence. Remember that for next time :-)

No, because SUM will want to consolidate on all elements of the key. Your data will be in the correct order for SUM processing to work, but it is not going to "SUM" on 1,8, but on 1,16.

Just do the SORT as one key, ascending. Then your data is in sequence, and you follow that with OUTFIL and the reporting features to give you want you want (the last item in the sequence within the SORT key) and you will have what you want.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Displaying Largest value in a field and removing duplica

Postby Sanjana » Wed Jan 11, 2017 12:39 pm

Thanks for your help :)
Sanjana
 
Posts: 11
Joined: Sat Dec 10, 2016 11:42 am
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post