Page 1 of 1

How can i to do this with DFSORT?

PostPosted: Thu Aug 01, 2019 12:15 pm
by kregen
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

Re: How can i to do this with DFSORT?

PostPosted: Thu Aug 01, 2019 9:24 pm
by sergeyken
 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 **

Re: How can i to do this with DFSORT?

PostPosted: Thu Aug 01, 2019 10:24 pm
by sergeyken
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  

Re: How can i to do this with DFSORT?

PostPosted: Fri Aug 02, 2019 11:12 am
by kregen
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

Re: How can i to do this with DFSORT?

PostPosted: Fri Aug 02, 2019 4:46 pm
by sergeyken
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...

Re: How can i to do this with DFSORT?

PostPosted: Fri Aug 02, 2019 7:44 pm
by enrico-sorichetti
the output must contain all records for the group with the highest date

Re: How can i to do this with DFSORT?

PostPosted: Mon Aug 05, 2019 7:05 pm
by sergeyken
*                                            
 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

Re: How can i to do this with DFSORT?

PostPosted: Thu Aug 15, 2019 1:48 pm
by kregen
Hello sergeyken,
that is the solution i like..

Thanks a lot.

Regards
Kai