|
View previous topic :: View next topic
|
| Author |
Message |
skgupta81
New User
Joined: 11 Nov 2008
Posts: 27
Location: Canada
|
|
|
|
I have a situation and can't find a solution since morning.
Can you please help me with below...
I have a file
ABC123DA11
ABC123DA12
ABC123DA13
ABC123DA14
ABC123DA15
DEF345DA21
DEF345DA22
DEF345DA23
DEF345DA24
where position 1,6 is a key and 7,10 are data.
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.
I hope, I was clear. Thanks for your help. |
|
| Back to top |
|
 |
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
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.
Your records will look like this:
| Code: |
ABC123DA11 DA11
ABC123DA12 DA12
ABC123DA13 DA13
ABC123DA14 DA14
ABC123DA15 DA15 |
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:
| Code: |
ABC123 DA11
ABC123 DA11 DA12
ABC123 DA11 DA12 DA13
ABC123 DA11 DA12 DA13 DA14
ABC123 DA11 DA12 DA13 DA14 DA15 |
If you reach here then there should be no problem in filtering the record you want i.e. the last one with all the data parts.
Try and post if you face any difficulty.
. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
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.
Have a look at this one. |
|
| Back to top |
|
|
hailashwin
New User
Joined: 16 Oct 2008
Posts: 74
Location: Boston
|
|
|
|
I tried Bill's steps and here is the working code 
| Code: |
//SYSIN DD *
OPTION COPY
INREC IFTHEN=(WHEN=INIT,OVERLAY=(20:SEQNUM,1,ZD,RESTART=(1,6))),
IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'1'),RECORDS=5,
PUSH=(21:7,4)),
IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'2'),RECORDS=4,
PUSH=(31:7,4)),
IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'3'),RECORDS=3,
PUSH=(41:7,4)),
IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'4'),RECORDS=2,
PUSH=(51:7,4)),
IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'5'),RECORDS=1,
PUSH=(61:7,4))
OUTFIL REMOVECC,
NODETAIL,
SECTIONS=(1,6,
TRAILER3=(1,6,21,4,31,4,41,4,51,4,61,4))
/*
|
Thanks,
Ashwin. |
|
| Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
FWIW, something else to think about if the number of records in a group are equal (not for this example).
Use RESIZE and make it easy.
| Code: |
//STEP0001 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD DISP=SHR,DSN=INPUT/FB10
//OUT DD SYSOUT=*
//TOOLIN DD *
RESIZE FROM(IN) TO(OUT) TOLEN(50) -
USING(CTL1)
/*
//CTL1CNTL DD *
OUTFIL FNAMES=OUT,BUILD=(1,10,17,04,27,04,37,04,47,04)
/*
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//* |
Thanks, |
|
| Back to top |
|
|
skgupta81
New User
Joined: 11 Nov 2008
Posts: 27
Location: Canada
|
|
|
|
| Thanks.. I will try the above solutions quickly and let you know the outcome... |
|
| Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
| Quote: |
| Use RESIZE and make it easy. |
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.
. |
|
| Back to top |
|
|
skgupta81
New User
Joined: 11 Nov 2008
Posts: 27
Location: Canada
|
|
|
|
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.
FOR EX.
input file:
XYZ345DA10
ABC123DA11
ABC123DA12
DEF678DA21
MNO345DA33
PQR345DA44
output file:
XYZ345DA10 DA10
ABC123DA11 DA11
ABC123DA12 DA11 DA12
DEF678DA21 DA21 DA12
MNO345DA33 DA33 DA12
PQR345DA44 DA44 DA12
Well, this is how my actual control card looks like for the INREC part, where 1,140 is the key and then the data is at 141,164 (24char)
OPTION COPY
INREC IFTHEN=(WHEN=INIT,OVERLAY=(300:SEQNUM,1,ZD,RESTART=(1,140))),
IFTHEN=(WHEN=GROUP,BEGIN=(300,1,CH,EQ,C'1'),RECORDS=5,
PUSH=(165:141,24)),
IFTHEN=(WHEN=GROUP,BEGIN=(300,1,CH,EQ,C'2'),RECORDS=4,
PUSH=(190:141,24)),
IFTHEN=(WHEN=GROUP,BEGIN=(300,1,CH,EQ,C'3'),RECORDS=3,
PUSH=(213:141,24)),
IFTHEN=(WHEN=GROUP,BEGIN=(300,1,CH,EQ,C'4'),RECORDS=2,
PUSH=(237:141,24)),
IFTHEN=(WHEN=GROUP,BEGIN=(300,1,CH,EQ,C'5'),RECORDS=1,
PUSH=(261:141,24))
Will try 'RahulG31' method now.
| hailashwin wrote: |
I tried Bill's steps and here is the working code
| Code: |
//SYSIN DD *
OPTION COPY
INREC IFTHEN=(WHEN=INIT,OVERLAY=(20:SEQNUM,1,ZD,RESTART=(1,6))),
IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'1'),RECORDS=5,
PUSH=(21:7,4)),
IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'2'),RECORDS=4,
PUSH=(31:7,4)),
IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'3'),RECORDS=3,
PUSH=(41:7,4)),
IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'4'),RECORDS=2,
PUSH=(51:7,4)),
IFTHEN=(WHEN=GROUP,BEGIN=(20,1,CH,EQ,C'5'),RECORDS=1,
PUSH=(61:7,4))
OUTFIL REMOVECC,
NODETAIL,
SECTIONS=(1,6,
TRAILER3=(1,6,21,4,31,4,41,4,51,4,61,4))
/*
|
Thanks,
Ashwin. |
|
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Sorry, forgot: After the WHEN=GROUPs you need WHEN=(logicalexpression)s, again testing the SEQ.
If one, clobber the places for two, three and four.
If two, clobber for three and four.
If three, clobber for four.
Clobber means set it to space. |
|
| Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
This is what I had:
| Code: |
SORT FIELDS=COPY
INREC IFTHEN=(WHEN=INIT,OVERLAY=(15:SEQNUM,2,ZD,RESTART=(1,6))),
IFTHEN=(WHEN=(15,2,ZD,EQ,01),OVERLAY=(20:7,4)),
IFTHEN=(WHEN=(15,2,ZD,EQ,02),OVERLAY=(25:7,4)),
IFTHEN=(WHEN=(15,2,ZD,EQ,03),OVERLAY=(30:7,4)),
IFTHEN=(WHEN=(15,2,ZD,EQ,04),OVERLAY=(35:7,4)),
IFTHEN=(WHEN=(15,2,ZD,EQ,05),OVERLAY=(40:7,4))
OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(20,4,CH,NE,C' '),KEYBEGIN=(1,6),
PUSH=(20:20,4)),
IFTHEN=(WHEN=GROUP,BEGIN=(25,4,CH,NE,C' '),KEYBEGIN=(1,6),
PUSH=(25:25,4)),
IFTHEN=(WHEN=GROUP,BEGIN=(30,4,CH,NE,C' '),KEYBEGIN=(1,6),
PUSH=(30:30,4)),
IFTHEN=(WHEN=GROUP,BEGIN=(35,4,CH,NE,C' '),KEYBEGIN=(1,6),
PUSH=(35:35,4)),
IFTHEN=(WHEN=GROUP,BEGIN=(40,4,CH,NE,C' '),KEYBEGIN=(1,6),
PUSH=(40:40,4))
OUTFIL REMOVECC,
NODETAIL,
SECTIONS=(1,6,
TRAILER3=(1,6,20,4,25,4,30,4,35,4,40,4)) |
|
|
| Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
You also have an option to use old school SPLICE method. Output will be sorted on 1,6 position. Hopefully i have covered all possible situations .
If we can request one feature to be added to existing set of functionalities, RESIZE using ON fields would top my list.
| Code: |
//STEP0001 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD DISP=SHR,DSN=INPUT/FB10
//OUT DD SYSOUT=*
//TOOLIN DD *
SPLICE FROM(IN) TO(OUT) ON(1,6,CH) WITHEACH -
WITH(11,4) WITH(15,4) WITH(19,4) WITH(23,4) -
USING(CTL1)
/*
//CTL1CNTL DD *
INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,6),PUSH=(81:SEQ=1)),
IFTHEN=(WHEN=(81,1,ZD,EQ,2),OVERLAY=(11:7,4)),
IFTHEN=(WHEN=(81,1,ZD,EQ,3),OVERLAY=(15:7,4)),
IFTHEN=(WHEN=(81,1,ZD,EQ,4),OVERLAY=(19:7,4)),
IFTHEN=(WHEN=(81,1,ZD,EQ,5),OVERLAY=(23:7,4))
OUTFIL FNAMES=OUT,BUILD=(1,26)
/*
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//* |
Thanks, |
|
| Back to top |
|
|
skgupta81
New User
Joined: 11 Nov 2008
Posts: 27
Location: Canada
|
|
|
|
Yes, it worked now. I will also try other guy's suggestions to learn more.
Thanks everyone.
| Bill Woodger wrote: |
Sorry, forgot: After the WHEN=GROUPs you need WHEN=(logicalexpression)s, again testing the SEQ.
If one, clobber the places for two, three and four.
If two, clobber for three and four.
If three, clobber for four.
Clobber means set it to space. |
|
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
