IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

REMOVE DUPLICATE RECORDS BASED ON A SPECIFIC CONDITION


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
brindhamohan

New User


Joined: 11 May 2016
Posts: 6
Location: USA

PostPosted: Fri Dec 18, 2020 5:40 am
Reply with quote

Hi,
I have special condition while removing the duplicate records. For a given household the policy details and vehicle details remains the same and only driver details and error code varies. If its a policy error or vehicle error i need to retain only the first record for the household; if its a driver error i need to retain all the records in that household.

Code:

INPUT:
COL1       COL2        COL3      COL4          COL5
<1 10 >    <11 20>    <21 30>    <31 40>       <41 44>
RECORD#1   Policy#1   Vehicle#1  Driver#1       PERR      /* household 1 */
RECORD#2   Policy#1   Vehicle#1  Driver#2       PERR      /* household 1 */
RECORD#3   Policy#1   Vehicle#1  Driver#3       PERR      /* household 1 */
RECORD#4   Policy#2   Vehicle#2  Driver#1       DERR      /* household 2 */
RECORD#5   Policy#2   Vehicle#2  Driver#2       DERR     /* household 2 */
RECORD#6   Policy#2   Vehicle#2  Driver#2       DERR     /* household 2 */
RECORD#7   Policy#3   Vehicle#3  Driver#1       VERR     /* household 3 */
RECORD#8   Policy#3   Vehicle#3  Driver#2       VERR     /* household 3 */

Note:
PERR is Policy Error
VERR is Vehicle Error
DERR is Driver Error

Code:

OUTPUT:
COL1       COL2        COL3      COL4          COL5
<1 10 >    <11 20>    <21 30>    <31 40>       <41 44>
RECORD#1   Policy#1   Vehicle#1  Driver#1       PERR      /* household 1 */
RECORD#4   Policy#2   Vehicle#2  Driver#1       DERR      /* household 2 */
RECORD#5   Policy#2   Vehicle#2  Driver#2       DERR     /* household 2 */
RECORD#6   Policy#2   Vehicle#2  Driver#2       DERR     /* household 2 */
RECORD#7   Policy#3   Vehicle#3  Driver#1       VERR     /* household 3 */

Can someone help me how to achieve this?

Thanks![/code]
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1222
Location: Bamberg, Germany

PostPosted: Fri Dec 18, 2020 10:45 am
Reply with quote

Code:
  OPTION COPY                                                           
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,4,ZD,RESTART=(11,10)))     
  OUTFIL FNAMES=(SORTOUT),                                             
   INCLUDE=((81,4,ZD,EQ,+1,AND,41,1,SS,EQ,C'PV'),OR,(41,1,CH,EQ,C'D')),
    BUILD=(1,80)                                                       
  END
Back to top
View user's profile Send private message
brindhamohan

New User


Joined: 11 May 2016
Posts: 6
Location: USA

PostPosted: Fri Dec 18, 2020 8:46 pm
Reply with quote

Thank you so much! It worked icon_smile.gif Can you please explain the code?

Code:

INREC IFTHEN=(WHEN=INIT, OVERLAY=(81:SEQNUM,4,ZD,RESTART=(11,10)))


In the input record you are adding SEQNUM in column 81 to 84. What does RESTART means? i see it has something to do with policy# based on the column position 11.

Code:

OUTFIL FNAMES=(SORTOUT),
INCLUDE=((81,4,ZD,EQ,+1,AND,41,1,SS,EQ,C'PV'),OR,(41,1,CH,EQ,C'D'))


Here you are populating the output file based on the condition in the INCLUDE statement.
(41,1,CH,EQ,C'D') - if its driver error you are populating all the records to the output.
(81,4,ZD,EQ,+1,AND,41,1,SS,EQ,C'PV') - Why are we using +1 in the Sequence check and when we use SS will it check for P or V in the 41st position?
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1222
Location: Bamberg, Germany

PostPosted: Fri Dec 18, 2020 8:54 pm
Reply with quote

It's pretty easy. Your Input is pre-sorted, so we can assign an ascending number (col. 81) to every Policy. The value restarts with every new Policy number. Next is to ask for the first PERR/VERR or anything DERR.
If your input is not pre-sorted, you just replace INREC with OUTREC and SORT in advance. The logic should be the same.
Back to top
View user's profile Send private message
brindhamohan

New User


Joined: 11 May 2016
Posts: 6
Location: USA

PostPosted: Fri Dec 18, 2020 9:09 pm
Reply with quote

Thanks again!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Duplicate transid's declared using CEDA CICS 3
No new posts Sortjoin and Search for a String and ... DFSORT/ICETOOL 1
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Remove leading zeroes SYNCSORT 4
Search our Forums:

Back to Top