Merging records



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Merging records

Postby Puffe » Fri Apr 20, 2012 1:19 pm

Hi,

I want to merge records from different records with the same keyvalue and I wonder hos this can be done.

My file is fixed blocked with recordlength 11 and the key i position 1-4 and a numeric value in position 5-11.
The file contains following records:
2009 4520
2010 4701
2011 6613
2009 416
2010 388
2011 614
2009 257
2010 235
2011 260

I want all records that have key 2009 to look like this (from the three records)
2009 4520 416 257
and the same for 2010 and 2011
2010 4701 388 235
2011 6613 614 260

Mike
Puffe
 
Posts: 21
Joined: Mon May 02, 2011 1:41 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merging records

Postby BillyBoyo » Fri Apr 20, 2012 3:48 pm

I saw something a bit similar the other day, so I have adapted the SPLICE solution from that.

I "sorted" your input, which would need to be done, and can be included in this step. I don't know if ICETOOL is OK for you.

//S1 EXEC PGM=ICETOOL
//TOOLMSG  DD SYSOUT=*
//DFSMSG   DD SYSOUT=*
//OUT      DD SYSOUT=*
//TOOLIN   DD *
  SPLICE FROM(IN) TO(OUT) ON(1,4,CH) WITHANY USING(CTL1) -
  WITH(13,7) WITH(21,7) WITH(29,7)
//*
//CTL1CNTL DD *
  OPTION COPY
  INREC IFOUTLEN=36,
  IFTHEN=(WHEN=INIT,OVERLAY=(37:SEQNUM,2,ZD,RESTART=(1,4))),
  IFTHEN=(WHEN=(37,2,ZD,EQ,01),OVERLAY=(13:5,7)),
  IFTHEN=(WHEN=(37,2,ZD,EQ,02),OVERLAY=(21:5,7)),
  IFTHEN=(WHEN=(37,2,ZD,EQ,03),OVERLAY=(29:5,7))
  OUTFIL BUILD=(1,4,X,13,7,X,21,7,X,29,7)
//IN       DD *
2009   4520
2009    416
2009    257
2010   4701
2010    388
2010    235
2011   6613
2011    614
2011    260


Gives:

2009    4520     416     257
2010    4701     388     235
2011    6613     614     260
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Merging records

Postby skolusu » Fri Apr 20, 2012 10:11 pm

Puffe,

if your input always have 3 records per group , you can use the RESIZE operator to merge the records like shown below
//STEP0100 EXEC PGM=ICETOOL                     
//TOOLMSG  DD SYSOUT=*                         
//DFSMSG   DD SYSOUT=*                         
//IN       DD *                                 
----+----1----+----2----+----3----+----4----+---
2009 4520                                       
2010 4701                                       
2011 6613                                       
2009 416                                       
2010 388                                       
2011 614                                       
2009 257                                       
2010 235                                       
2011 260                                       
//OUT      DD SYSOUT=*                         
//TOOLIN   DD *                                 
  RESIZE FROM(IN) TO(OUT) TOLEN(33) USING(CTL1)
//CTL1CNTL DD *                                 
  SORT FIELDS=(1,4,CH,A),EQUALS                 
  INREC BUILD=(1,11)                           
  OUTFIL BUILD=(1,11,16,7,27,7)                 
//*


The output from the above is
2009 4520   416    257
2010 4701   388    235
2011 6613   614    260
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post