|
View previous topic :: View next topic
|
| Author |
Message |
brindhamohan
New User
Joined: 11 May 2016
Posts: 12
Location: USA
|
|
|
|
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 |
|
 |
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1442
Location: Bamberg, Germany
|
|
|
|
| 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 |
|
|
brindhamohan
New User
Joined: 11 May 2016
Posts: 12
Location: USA
|
|
|
|
Thank you so much! It worked  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 |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1442
Location: Bamberg, Germany
|
|
|
|
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 |
|
|
brindhamohan
New User
Joined: 11 May 2016
Posts: 12
Location: USA
|
|
|
|
| Thanks again! |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
