IBM Mainframe Forum

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

Select all

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

Select all

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

by **sergeyken** » Thu Aug 01, 2019 9:24 pm

Select all

  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 

Select all

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

by **sergeyken** » Thu Aug 01, 2019 10:24 pm

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

Select all

 *                                                                      
 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   

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

by **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...

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

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

by **sergeyken** » Mon Aug 05, 2019 7:05 pm

Select all

*                                             
 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

Select all

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

by **kregen** » Thu Aug 15, 2019 1:48 pm

Hello sergeyken,
that is the solution i like..

Thanks a lot.

Regards
Kai

IBM Mainframe Forum

How can i to do this with DFSORT?

How can i to do this with DFSORT?

Re: How can i to do this with DFSORT?

Re: How can i to do this with DFSORT?

Re: How can i to do this with DFSORT?

Re: How can i to do this with DFSORT?

Re: How can i to do this with DFSORT?

Re: How can i to do this with DFSORT?

Re: How can i to do this with DFSORT?