How can i to do this with DFSORT?



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

How can i to do this with DFSORT?

Postby kregen » Thu Aug 01, 2019 12:15 pm

Hello togehter,
i like to fetch the all records with the highest date for a group - id.

Input file:
1 - 3 = group-ID
4 - 13 = date
14 - 15 = sequencenumber
16 - 99 = Data


AAA 01.01.2019 01 other data
AAA 01.01.2019 02 other data
AAA 01.01.2019 03 other data
AAA 01.01.2019 04 other data
AAA 02.01.2019 01 other data
AAA 02.01.2019 02 other data
AAA 02.01.2019 03 other data
AAA 02.01.2019 04 other data
AAA 02.01.2019 05 other data
AAA 01.02.2019 01 other data
AAA 01.02.2019 02 other data
AAA 01.02.2019 03 other data
AAA 01.02.2019 04 other data
AAA 08.03.2019 01 other data
AAA 08.03.2019 02 other data
AAA 08.03.2019 03 other data
AAA 08.03.2019 04 other data
AAA 08.03.2019 05 other data
BBB 01.02.2019 01 other data
BBB 02.03.2019 01 other data
BBB 02.03.2019 02 other data
BBB 02.03.2019 03 other data
BBB 02.03.2019 04 other data
BBB 02.04.2019 05 other data
BBB 01.04.2019 01 other data
BBB 01.04.2019 02 other data
BBB 01.04.2019 03 other data
BBB 01.04.2019 04 other data
BBB 08.05.2019 01 other data
BBB 08.05.2019 02 other data
BBB 08.05.2019 03 other data
 


the output must contain all records for the group with the highest date

AAA 08.03.2019 01 other data
AAA 08.03.2019 02 other data
AAA 08.03.2019 03 other data
AAA 08.03.2019 04 other data
AAA 08.03.2019 05 other data
BBB 08.05.2019 01 other data
BBB 08.05.2019 02 other data
BBB 08.05.2019 03 other data
 


can i do this with dfsort?

thanks for support

regards
Kai
kregen
 
Posts: 6
Joined: Wed Jan 23, 2008 6:43 pm
Has thanked: 3 times
Been thanked: 0 time

Re: How can i to do this with DFSORT?

 

Re: How can i to do this with DFSORT?

Postby sergeyken » Thu Aug 01, 2019 9:24 pm

 INREC BUILD=(1,80,      original record                              
              11,4,      append YYYY                                  
              8,2,       append MM                                    
              5,2,       append DD                                    
              1,3,       append AAA-BBB-...                            
             16,2)       append 01-02-03-...                          
*                                                                      
 SORT FIELDS=(1,3,CH,A,   sort on AAA-BBB-...                          
             16,2,CH,A,   sort on 01-02-03-...                        
             81,6,CH,A)   sort on YYYYMMDD                            
*                                                                      
 OUTFIL NODETAIL,REMOVECC,                                            
        SECTIONS=(89,5,            group by AAA01                      
                  TRAILER3=(1,80)) restore last record's original part
*                                                                      
 END


********************************* TOP OF DATA ****
AAA 08.03.2019 01 other data                      
AAA 08.03.2019 02 other data                      
AAA 08.03.2019 03 other data                      
AAA 08.03.2019 04 other data                      
AAA 08.03.2019 05 other data                      
BBB 08.05.2019 01 other data                      
BBB 08.05.2019 02 other data                      
BBB 08.05.2019 03 other data                      
BBB 01.04.2019 04 other data                      
BBB 02.04.2019 05 other data                      
******************************** BOTTOM OF DATA **

These users thanked the author sergeyken for the post:
kregen (Fri Aug 02, 2019 11:13 am)
sergeyken
 
Posts: 9
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 0 time
Been thanked: 4 times

Re: How can i to do this with DFSORT?

Postby sergeyken » Thu Aug 01, 2019 10:24 pm

In this particular case the same can be done a little bit simpler:

*                                                                      
 SORT FIELDS=(1,3,CH,A,    sort on AAA                                
             16,2,CH,A,    sort on 01                                  
             11,4,CH,A,    sort on YYYY                                
              8,2,CH,A,    sort on MM                                  
              5,2,CH,A)    sort on DD                                  
*                                                                      
 OUTFIL NODETAIL,REMOVECC,                                            
        SECTIONS=(1,3,16,2,        group by AAA-01                    
                  TRAILER3=(1,80)) restore last record's original part
*                                                                      
 END  

These users thanked the author sergeyken for the post:
kregen (Fri Aug 02, 2019 11:12 am)
sergeyken
 
Posts: 9
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 0 time
Been thanked: 4 times

Re: How can i to do this with DFSORT?

Postby kregen » Fri Aug 02, 2019 11:12 am

Hello sergeyken,
thank you for the solution.

But it's not quite what I need yet. Please take a look at the result I expect.

I need all records with to a group-id with the highest date. the sequence is not important. only records to a date may be in the result.

Regards
Kai
kregen
 
Posts: 6
Joined: Wed Jan 23, 2008 6:43 pm
Has thanked: 3 times
Been thanked: 0 time

Re: How can i to do this with DFSORT?

Postby sergeyken » Fri Aug 02, 2019 4:46 pm

This example gives you EXACTLY what you asked about. After SORT operation, only the last rows of each group (with MAX date values) are listed; the rest of rows is eliminated by NODETAILS keyword.

Try to compare your desired output with the actual output given in my post - CHECK IT CAREFULLY. There is no difference between two of them...
sergeyken
 
Posts: 9
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 0 time
Been thanked: 4 times

Re: How can i to do this with DFSORT?

Postby enrico-sorichetti » Fri Aug 02, 2019 7:44 pm

the output must contain all records for the group with the highest date
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2864
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 153 times

Re: How can i to do this with DFSORT?

Postby sergeyken » Mon Aug 05, 2019 7:05 pm

*                                            
 SORT FIELDS=(1,3,CH,A,    sort on AAA        
             11,4,CH,D,    sort on YYYY      
              8,2,CH,D,    sort on MM        
              5,2,CH,D)    sort on DD        
*                                            
 OUTREC IFTHEN=(WHEN=GROUP,                  
                KEYBEGIN=(1,3),              
                PUSH=(41:5,10))              
*                                            
 OUTFIL INCLUDE=(5,10,CH,EQ,41,10,CH),        
        BUILD=(1,40)                          
*                                            
 END

AAA 08.03.2019 01 other data  
AAA 08.03.2019 02 other data  
AAA 08.03.2019 03 other data  
AAA 08.03.2019 04 other data  
AAA 08.03.2019 05 other data  
BBB 08.05.2019 01 other data  
BBB 08.05.2019 02 other data  
BBB 08.05.2019 03 other data

These users thanked the author sergeyken for the post:
kregen (Thu Aug 15, 2019 1:47 pm)
sergeyken
 
Posts: 9
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 0 time
Been thanked: 4 times

Re: How can i to do this with DFSORT?

Postby kregen » Thu Aug 15, 2019 1:48 pm

Hello sergeyken,
that is the solution i like..

Thanks a lot.

Regards
Kai
kregen
 
Posts: 6
Joined: Wed Jan 23, 2008 6:43 pm
Has thanked: 3 times
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post