Sort and overlay with condition on previous record



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

Sort and overlay with condition on previous record

Postby jyothp12 » Mon Feb 18, 2019 6:52 pm

Hi ,

I tried to search the existing topics and couldn't find a solution for my problem, and that is the reason for posting this as a new topic.
Please let me know the link if the topic is already existing/discussed.

I have below requirement -
If the key is a particluar value in the input file of record length 80, the field from 70 to 72 (3 characters) should have value 100. Next time when the same key comes, the field should be updated with 101. The third time when the key comes it should be 100. So the records having a prticular key should have 100 or 101 at position 70 alternatively.

I am confused how to know what was the last value overlayed (100 or 101) so that I can overlay the other for current record.

For example -
Input file (fixed length)
ABC ------------- XXX
XYZ --------------XXX
CVF --------------XXX
ABC ------------- XXX
XYZ --------------XXX
CVF --------------XXX
XYZ --------------XXX
ABC ------------- XXX
CVF --------------XXX
ABC ------------- XXX
ABC ------------- XXX


Output file (fixed length)
ABC ------------- 100
XYZ --------------XXX
CVF --------------XXX
ABC ------------- 101
XYZ --------------XXX
CVF --------------XXX
XYZ --------------XXX
ABC ------------- 100
CVF --------------XXX
ABC ------------- 101
ABC ------------- 100
 


Please help.

Thanks in Advance !!
Jyothi
jyothp12
 
Posts: 14
Joined: Thu Dec 22, 2011 12:37 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Sort and overlay with condition on previous record

Postby Garry F Carroll » Mon Feb 18, 2019 8:36 pm

One possible solution with several passes over the data would be to:
1 Append sequence number (so you can put records back in order later)
*                                
 OPTION COPY                      
 INREC  BUILD=(1,80,SEQNUM,6,ZD)  
*                                
 

2 Sort the records so all the same ids are together and add another sequence number
*                                          
 SORT FIELDS=(1,3,CH,A,81,6,CH,A)          
*                                          
 OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,3),  
                 PUSH=(87:SEQ=5))          
*                                          
 

Based on the second sequence number, assign the '100' or '101'
*                                                            
 SORT FIELDS=(87,5,CH,A,81,6,CH,A)                            
*                                                            
 OUTREC IFTHEN=(WHEN=(91,1,CH,EQ,C'1',OR,                    
                       91,1,CH,EQ,C'3',OR,                    
                       91,1,CH,EQ,C'5',OR,                    
                       91,1,CH,EQ,C'7',OR,                    
                       91,1,CH,EQ,C'9'),OVERLAY=(70:C'100')),
*                                                            
        IFTHEN=(WHEN=(91,1,CH,EQ,C'0',OR,                    
                       91,1,CH,EQ,C'2',OR,                    
                       91,1,CH,EQ,C'4',OR,                    
                       91,1,CH,EQ,C'6',OR,                    
                       91,1,CH,EQ,C'8'),OVERLAY=(70:C'101'))  
*                                                            
 


then, sort into original order based on the first sequence number and drop both sequence numbers
*                                  
 SORT FIELDS=(81,6,CH,A,1,3,CH,A)  
*                                  
 OUTREC  BUILD=(1,80)              
*                                
 


There's probably a better way to do it.

Garry.
Garry F Carroll
 
Posts: 28
Joined: Wed Sep 19, 2018 8:20 pm
Has thanked: 0 time
Been thanked: 1 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post