Using Syncsort 1.4.0.1R, I would like to limit the number of records in the output that have a particular combination of values for FIELD1 and FIELD2 e.g. in the data below, the particular combinations are
1) "MONKEY" and "YES"
2) "MONKEY" and "NO"
3) "SAM" and "YES"
4) "SAM" and "NO"
Code:
FIELD1 FIELD2
MONKEY YES
MONKEY YES
MONKEY YES
MONKEY YES
MONKEY YES
MONKEY NO
SAM YES
SAM YES
SAM NO
SAM NO
SAM NO
SAM NO
SAM NO
If I wanted to limit of 2records/combination, the output should look like:
Code:
FIELD1 FIELD2
MONKEY YES
MONKEY YES
MONKEY NO
SAM YES
SAM YES
SAM NO
SAM NO
One approach to this problem, would be to:
1) Sort by FIELD1, then FIELD2
2) Add a Sequence Number, that restarts whenever FIELD2 Changes
3) Include only records that have a Sequence number less than 3.
This approach falls flat if a particular combination is missing - if the "MONKEY NO" record is missing in the input, then then we go from one combination ("MONKEY YES") to another (SAM YES) without the sequence number restarting. Any suggestions? If I need to be more clear about what I am asking, please let me know.
The first control field can have 101 different values; the second control field will always be one of two values, but never blank.
Bill,
The actual data differs from what was posted in that the control fields are non-contiguous. The records are fixed length and the Control fields are always in the same position.
I suppose I can reformat the record to make the two control fields contiguous and then use SEQNUM with the RESTART parameter on that combined control field. I think this is the solution. Thank you both for your help! I'll post some sample data and code for future reference in a future post.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Yes, I think that will do. Your sample data shows that the key fields are contiguous, even if not in sorted order. If they are non-contiguous, you will have to SORT, otherwise I think you have the correct approach.
Task: Limit unique combinations of non-contiguous control fields (fields 1 and 3) to two records each using Syncsort.
Input Text
Code:
Field1 Field2 Field3
MONKEY NONSENSE YES
MONKEY NONSENSE YES
MONKEY NONSENSE YES
MONKEY NONSENSE YES
MONKEY NONSENSE YES
MONKEY MEANINGLESS YES
MONKEY MEANINGLESS YES
MONKEY MEANINGLESS MAYBE
MONKEY MEANINGLESS NO
MONKEY MEANINGLESS NO
MONKEY MEANINGLESS NO
SAM MEANINGLESS YES
SAM MEANINGLESS MAYBE
SAM MEANINGLESS MAYBE
SAM MEANINGLESS MAYBE
SAM MEANINGLESS MAYBE
SAM MEANINGLESS NO
SAM MEANINGLESS NO
SAM MEANINGLESS NO
SAM MEANINGLESS NO
SAM MEANINGLESS NO
Output:
Code:
MONKEY NONSENSE YES
MONKEY NONSENSE YES
MONKEY MEANINGLESS MAYBE
MONKEY MEANINGLESS NO
SAM MEANINGLESS YES
SAM MEANINGLESS MAYBE
SAM MEANINGLESS MAYBE
SAM MEANINGLESS NO
SAM MEANINGLESS NO
