Page 1 of 2

How to Split the record in two part and remove the duplicate

PostPosted: Sun Sep 16, 2012 2:02 pm
by mainframegeek
How to Split the record in two part and remove the duplicate using syncsort?
File 1
01 FILE-REC
05 AREA-NMBR PIC 9(5)
05 BEG-RANGE PIC 9(10)
05 END-RANGE PIC 9(10)

DATA IN FILE 1

0001952000000000100000008
0001952000000001000000020
0001952000000003000000040
0012121000000011100000121
0012121000000015100000191

OUTPUT FILE
0001952000000000100000040
0012121000000011100000191

REQUIREMENT
For a particular area number we have more than one record, out of these records we have to arrange minimum value for BEG-RANGE field and maximum value for END-RANGE in a record.

as per example values of BEG-RANGE for 0001952 AREA-NMBR
0000000001
0000000010
0000000030

out of these we need to get minimum value 0000000001

as per example values of END-RANGE for 0001952 AREA-NMBR
00000008
00000020
00000040
out of these we need to get maximum value 0000000040

and at last we need to put these two values with AREA-NMBR
like 000195200000000010000000040

Likewise we have to do for other records. Area-NMBR is getting repeated but other two fields are unique.
Can anyone help me doing this with the help of synsort 1.3.2 version.

Re: How to Split the record in two part and remove the dupli

PostPosted: Sun Sep 16, 2012 3:37 pm
by BillyBoyo
Puneet,

Do you have to take the beginning range from the first record for that key and the ending range from the last record for the key?

Or, are you OK with the MINimum beginning value for the key and the MAXimum ending value for the key.

As you already know, the first is difficult and the second is easy.

Re: How to Split the record in two part and remove the dupli

PostPosted: Sun Sep 16, 2012 4:25 pm
by mainframegeek
Values could be arranged in any order.
For Example, this is given above
0001952000000000100000008
0001952000000001000000020
0001952000000003000000040

It could be in any order.Like given below
0001952000000003000000040
0001952000000001000000020
0001952000000000100000008
Output should be
0001952000000001000000040

The purpose is to arrange the records in such a way that minimum value of BEG-RANGE and maximum range of END-RANGE will come for that particular area number in a single record. After doing that single record has to be there, which should have minimum value BEG-RANGE and maximum value of END-RANGE.

Re: How to Split the record in two part and remove the dupli

PostPosted: Sun Sep 16, 2012 9:49 pm
by BillyBoyo
And did you get to try the OUTFIL with SECTIONS and TRAILER3 solution?

Re: How to Split the record in two part and remove the dupli

PostPosted: Sun Sep 16, 2012 10:38 pm
by mainframegeek
I tried with the solution given below and got the error:
JOINKEYS FILES=F1,FIELDS=(26,8,A),SORTED,NOSEQCK
*
JOINKEYS FILES=F2,FIELDS=(26,8,A),SORTED,NOSEQCK
*
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,25,F2:1,5)
INREC IFOUTLEN=26,
IFTHEN=(WHEN=GROUP,BEGIN=(1,5),PUSH=(32:6,10))
*
IFTHEN=(WHEN=(1,5,CH,NE,26,5,CH,OR,31,1,ZD,EQ,1),
*
OVERLAY=(6:32,10,26:C'X'))
*
OUTFIL INCLUDE=(26,1,CH,EQ,C'X'),BUILD=(1,25)
WER268A JOINKEYS STATEMENT: SYNTAX ERROR
WER268A JOINKEYS STATEMENT: SYNTAX ERROR
WER251A INCLUDE/OMIT INVALID FORMAT
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE

Re: How to Split the record in two part and remove the dupli

PostPosted: Sun Sep 16, 2012 11:43 pm
by BillyBoyo
BillyBoyo wrote:And did you get to try the OUTFIL with SECTIONS and TRAILER3 solution?


You've been told that the JOINKEYS won't work. Let alone anything else, the JNFnCNTL files are not supported.

Re: How to Split the record in two part and remove the dupli

PostPosted: Mon Sep 17, 2012 1:35 pm
by bodatrinadh
Hi,

You can try this snippet..

//STEP2    EXEC PGM=SORT     
//SYSOUT   DD SYSOUT=*       
//SYSPRINT DD SYSOUT=*       
//SORTIN   DD *               
0001952000000000100000008     
0001952000000001000000020     
0001952000000003000000040     
0012121000000011100000121     
0012121000000015100000191     
//SORTOUT DD SYSOUT=*         
//SYSIN    DD *               
  SORT FIELDS=(1,7,CH,A)     
  DUPKEYS MAX=(18,08,ZD)     


Output :-

0001952000000000100000040   
0012121000000011100000191   

Re: How to Split the record in two part and remove the dupli

PostPosted: Mon Sep 17, 2012 2:13 pm
by BillyBoyo
-3nadh,

That's nice.

Puneet messed up the test data, so 1,5 and 6,10, 16,10 are the fields.

Does it work with MERGE as the file is already in key order?

OPTION EQUALS
MERGE FIELDS=(1,5,CH,A)
DUPKEYS MAX=(16,10,ZD)


Also, any other options on DUPKEYS? Presumably MIN. What about FIRST and LAST?

Is this for 1.4 only? Puneet has 1.3.2.

Re: How to Split the record in two part and remove the dupli

PostPosted: Mon Sep 17, 2012 3:27 pm
by bodatrinadh
Hi Billy,

If the test data has 1,5 and 6,10, 16,10 key fields then i need to try some alternate code...

Does it work with MERGE as the file is already in key order?


I tried your code Billy..

OPTION EQUALS
MERGE FIELDS=(1,5,CH,A)
DUPKEYS MAX=(16,10,ZD)


Output i got is
0001952000000003000000040
0012121000000015100000191


Also, any other options on DUPKEYS? Presumably MIN. What about FIRST and LAST?

Is this for 1.4 only? Puneet has 1.3.2.

DUPKEYS will work from 1.3.2 version.

Re: How to Split the record in two part and remove the dupli

PostPosted: Mon Sep 17, 2012 4:01 pm
by BillyBoyo
Excellent -3nadh,

Interesting with the MERGE - keeps the entire MAX record maybe?

I was wondering because with DFSort you can use MERGE with SORTIN01 only and then SUM FIELDS=NONE works with an already sorted file (can't do the SUM FIELDS=NONE with SORT FIELDS=NONE or OPTION,COPY).

Can you just add about other options for the DUPKEYS, please?