I need to merge the records based on key as per below
ABC123DA11DA12DA13DA14DA15
DEF345DA21DA22DA23DA24
That means, first 6 bytes will be the key and the data will be appended from all matching records and becomes single record. Other dups will be removed.
There could be max 5 match records, so if there are less records.. remaining data positions will be replaced with spaces.
Per me, It certainly can be done but definitely require some thinking.
Here are the steps (can be done in a single SORT/ICETOOL step):
1. Mark the records with sequence numbers inside a group. So, a group having 5 records will have seq numbers 1,2,3,4,5. Use RESTART to restart the seq numbers for each group.
2. Now, use OVERLAY to copy the data part of the record with seq num 1 to a different location (say column 15-18). And then, the data part of record 2 to location 20-23 and repeat the process for all the records in a group.
3. Now, use IFTHEN=(WHEN=GROUP for a group that begins with a non-blank character and with KEYBEGIN=(1,6) and PUSH the data parts. So now, your data will look like this:
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Unless SORT's MERGE works for you (it does not) you are not Merging records.
COPY operation.
WHEN=INIT with SEQNUM and RESTART (for the key) to a temporary extension of the record.
Four WHEN=GROUPs, with BEGIN for the first four values of the SEQNUM, to PUSH the data to specific locations for each GROUP. Specify RECORDS=5 on the first, =4 on the second, =3 then =2.
The fifth record now has all the data you want. If there is a fifth record. If there is not, the last record will have what data there is.
Use OUTFIL reporting functions, REMOVECC, NODETAIL, SECTIONS (specifying the key) and TRAILER3 to do a final arrangement of the data. Which will get you the last record in each group.
It's good knowledge. Smaller records can be combined into a larger record and larger records can be broken into smaller one. Although, as mentioned, not for this example but definitely something to keep in mind.
Below solution(Aswin's method) works only in case there are 5 records everytime for a key. but in my case, there could be 1 to 5 records for the key.
With below solution, when there are more than 1 but less than 5 match records for a key, it writes the data in all consecutive 5 records even if its not part of the key and creates invalid file in the end.
