Page 1 of 1

Assigning and Restarting sequence for a group

PostPosted: Wed Mar 11, 2015 9:45 pm
by EndisBeginner
Thank you for reading this.

I am trying to assign a sequence number for each group and then restart for the next group. Here's my data
Input
L|19500|19500053423|AAAAAAAAAAAAAAA|08/27/2009|001|
L|19500|19500053423|CCCCCCCCCCCCCCC|08/27/2009|002|
L|19500|19500128631|AAAAAAAAAAAAAAA|10/27/2010|003|
L|19500|19500128631|AAAAAAAAAAAAAAA|10/27/2010|003|
L|19500|19500128631|CCCCCCCCCCCCCCC|10/27/2010|004|
L|19500|19500128651|AAAAAAAAAAAAAAA|10/27/2010|005|
L|19500|19500128651|CCCCCCCCCCCCCCC|10/27/2010|006|
L|19500|19500128652|AAAAAAAAAAAAAAA|11/01/2012|007|
L|19500|19500128652|AAAAAAAAAAAAAAA|11/01/2012|007|
L|19500|19500128652|CCCCCCCCCCCCCCC|11/01/2012|008|

Last 3 digits are what I was able to get as a sequence. My syncsort card is creating sequence for each group and doing it right, however I am not able to restart the sequence for each group.

Desired output

L|19500|19500053423|AAAAAAAAAAAAAAA|08/27/2009|001|
L|19500|19500053423|CCCCCCCCCCCCCCC|08/27/2009|002|
L|19500|19500128631|AAAAAAAAAAAAAAA|10/27/2010|001|
L|19500|19500128631|AAAAAAAAAAAAAAA|10/27/2010|001|
L|19500|19500128631|CCCCCCCCCCCCCCC|10/27/2010|002|
L|19500|19500128651|AAAAAAAAAAAAAAA|10/27/2010|001|
L|19500|19500128651|CCCCCCCCCCCCCCC|10/27/2010|002|
L|19500|19500128652|AAAAAAAAAAAAAAA|11/01/2012|001|
L|19500|19500128652|AAAAAAAAAAAAAAA|11/01/2012|001|
L|19500|19500128652|CCCCCCCCCCCCCCC|11/01/2012|002|

Syncsort Card:

SORT FIELDS=COPY
INREC IFTHEN=(WHEN=INIT,OVERLAY=(572:SEQNUM,3,ZD,RESTART=(09,27))),
IFTHEN=(WHEN=GROUP,BEGIN=(572,3,ZD,EQ,1),PUSH=(48:ID=3))
OUTFIL FILES=01,
OUTREC=(1,574)

Input File Length is 571.

Any help on how to restart the sequence for each group is greatly appreciated.

Thank you.

Re: Assigning and Restarting sequence for a group

PostPosted: Wed Mar 11, 2015 10:40 pm
by BillyBoyo
 SORT FIELDS=COPY
 INREC IFTHEN=(WHEN=INIT,OVERLAY=(572:SEQNUM,3,ZD,RESTART=(09,27))),
           IFTHEN=(WHEN=GROUP,BEGIN=(572,3,ZD,EQ,1),PUSH=(48:SEQ=3))
 OUTFIL FILES=01,
             BUILD=(1,574)


You can try that. Note I've changed your OUTREC on OUTFIL to BUILD. Saves confusion, as OUTREC has another meaning as well. SEQ=3 will give you a three-digit sequence number within the group.

After trying that, try this:

 SORT FIELDS=COPY
 INREC IFTHEN=(WHEN=INIT,OVERLAY=(48:SEQNUM,3,ZD,RESTART=(09,27)))
 OUTFIL FILES=01


The RESTART is already giving you the sequence number you want, as it starts again from (default) one when the key changes.

Re: Assigning and Restarting sequence for a group

PostPosted: Wed Mar 11, 2015 11:04 pm
by EndisBeginner
Thank you BillyBoyo. Much appreciated!

With the first card , I am getting the output as below:

L|19500|19500053423|AAAAAAAAAAAAAAA|08/27/2009|001|
L|19500|19500053423|CCCCCCCCCCCCCCC|08/27/2009|001|
L|19500|19500128631|AAAAAAAAAAAAAAA|10/27/2010|001|
L|19500|19500128631|AAAAAAAAAAAAAAA|10/27/2010|002|
L|19500|19500128631|CCCCCCCCCCCCCCC|10/27/2010|001|
L|19500|19500128651|AAAAAAAAAAAAAAA|10/27/2010|001|
L|19500|19500128651|CCCCCCCCCCCCCCC|10/27/2010|001|
L|19500|19500128652|AAAAAAAAAAAAAAA|11/01/2012|001|
L|19500|19500128652|AAAAAAAAAAAAAAA|11/01/2012|002|
L|19500|19500128652|CCCCCCCCCCCCCCC|11/01/2012|001|

While this is not what is desired, I should explain my issue better. Positions 9 through 19 is key1 and 21 through 35 is key2. So lets, say for example
key 1 is 19500128631. For this value of key1, there are three rows. When the value of key2 is 'AAAAAAAAAAAAAAA', Seq should be 1 i.e. this combination is considered one group and should have 001 for both rows. When the value of key2 is 'CCCCCCCCCCCCCCC', seq should be 2 i.e. 002

What I got using below card:
SORT FIELDS=COPY
INREC IFTHEN=(WHEN=INIT,OVERLAY=(572:SEQNUM,3,ZD,RESTART=(09,27))),
IFTHEN=(WHEN=GROUP,BEGIN=(572,3,ZD,EQ,1),PUSH=(48:SEQ=3))
OUTFIL FILES=01,
BUILD=(1,574)
L|19500|19500128631|AAAAAAAAAAAAAAA|10/27/2010|001|
L|19500|19500128631|AAAAAAAAAAAAAAA|10/27/2010|002|
L|19500|19500128631|CCCCCCCCCCCCCCC|10/27/2010|001|

But here is what I am trying to achieve:
L|19500|19500128631|AAAAAAAAAAAAAAA|10/27/2010|001|
L|19500|19500128631|AAAAAAAAAAAAAAA|10/27/2010|001|
L|19500|19500128631|CCCCCCCCCCCCCCC|10/27/2010|002|

Again, thank you much for your help.

Re: Assigning and Restarting sequence for a group

PostPosted: Wed Mar 11, 2015 11:56 pm
by EndisBeginner
I also tried out with the second sort card, but still not the desired output.

Syncsort card:

SORT FIELDS=COPY
INREC IFTHEN=(WHEN=INIT,OVERLAY=(48:SEQNUM,3,ZD,RESTART=(09,27)))
OUTFIL FILES=01

Output what I got:
L|19500|19500128631|AAAAAAAAAAAAAAA|10/27/2010|001|
L|19500|19500128631|AAAAAAAAAAAAAAA|10/27/2010|002|
L|19500|19500128631|CCCCCCCCCCCCCCC|10/27/2010|001|

Output desired:
L|19500|19500128631|AAAAAAAAAAAAAAA|10/27/2010|001|
L|19500|19500128631|AAAAAAAAAAAAAAA|10/27/2010|001|
L|19500|19500128631|CCCCCCCCCCCCCCC|10/27/2010|002|

Re: Assigning and Restarting sequence for a group

PostPosted: Thu Mar 12, 2015 3:14 am
by Terry Heinze
EndisBeginner,
In the future, please use code tags to improve readability.