MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

DFSORT/ICETOOL to Merge record on Keys

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
Mahesh_Gandla
Beginner


Joined: 06 Jul 2016
Posts: 4
Topics: 2
PostPosted: Thu Jul 07, 2016 5:12 am    Post subject: DFSORT/ICETOOL to Merge record on Keys Reply with quote
Hi,

My Input file has records in below format:
Col 1-5 is Product Name
Col 8-15 is Product Tran
Col 18-28 is Product Type

Input looks like below

Code:
----+----1----+----2----+----3
ADAAA  95-05929  00000001100
ADAAA  95-05961  00000001200
ADAAA  95-23385  00000001300
ADAAA  95-06462  00000001301
ADAAA  95-23414  00000001600
ADAAA  95-06219  00000001630
ADAAA  95-05988  00000004100
ADAAA  95-05953  00000004195
ADAAA  95-01637  00000004400
ADAAA  95-06542  00000004800
ADAAA  95-28477  00000006400
ADAAA  95-28469  00000006410
ADAAA  95-28485  00000008100
ADAAB  95-05929  00000001100
ADAAB  95-05961  00000001200
ADAAB  95-23385  00000001300
ADAAB  95-06462  00000001301
ADAAB  95-23414  00000001600
ADAAB  95-06219  00000001630
ADAAB  95-05988  00000004100
ADAAB  95-05953  00000004195
ADAAB  95-01637  00000004400
ADAAB  95-06542  00000004800
ADAAB  95-28477  00000006400
ADAAB  95-28469  00000006410
ADAAB  95-28485  00000008100
ADAAV  95-05988  00000004100
ADAAV  95-06542  00000004800
ADAEH  95-05929  00000001100
ADAEI  95-06737  00000008100


My output should merge on the basis of Product Name & Product Type Like
for ADAAA, it should be like
ADAAA followed by Prod tran 95-05929 (of Prod type 00000001100 ), followed by Prod tran 95-05961 (of Prod type 00000001200)....and so on.
Please note that :
For Product Name ADAAV we have Prod type 00000004100 & 00000004800 only (so its Prod trans should come in 7th and 10 position respectively)

For Product Name ADAEH we have Prod type 00000001100 only, so its prod tran should come in 1st position as below (all subsequent positions should be blank)
ADAEH 95-05929

For Product Name ADAEI we have Prod type 00000008100 only, so its prod tran should come in 13th position (all previous positions should be blank)
ADAEH 95-05929

So my Ouput should look like;

Code:
ADAAA  95-05929 95-05961 95-23385.........so on
ADAAB  95-05929 95-05961 95-23385.........so on
ADAAV  {first 6 pos blank) then 95-05988 (in 7th pos) then 8-9 pos blank, then 95-06542 (in 10th pos) then all other blanks}
ADAEH  95-05929
ADAEI   {first 12 pos blank followed by 95-06737 (in 13th pos)}


Please help me, how we can merge record on the basis of above criteria.

Thanks,
Mahesh
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
PostPosted: Thu Jul 07, 2016 9:58 am    Post subject: Reply with quote
Mahesh_Gandla,

1. Please stop the same question in multiple forums. I have deleted the duplicate topics.

Now coming to the request, Please answer the following questions

1. What is the LRECL and RECFM of the input file?
2. What is the maximum number of "Product type" do you have?
3. What is the order of the Product type? ie. how did you determine (00000004100 & 00000004800 should have rank 7 and 10 respectively) So if you have another product type 00000002222 will the rank of 00000004100 & 00000004800 change?
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Mahesh_Gandla
Beginner


Joined: 06 Jul 2016
Posts: 4
Topics: 2
PostPosted: Thu Jul 07, 2016 12:02 pm    Post subject: Reply with quote
Hi Kolusu,

Thanks for the prompt reply.
1) The input file record format is FB and LRECL is 70 (Col 1-5 is Product Name, Col 8-15 is Product Tran, Col 18-28 is Product Type, Col 29-70 are blank).
2) Max number of product type will be 13 (as given for Product Name ADAAA & ADAAB) in below order
00000001100
00000001200
00000001300
00000001301
00000001600
00000001630
00000004100
00000004195
00000004400
00000004800
00000006400
00000006410
00000008100

3) Order of Product type is mentioned above.
As 00000004100 in above mentioned Product type comes at no 7 & 00000004800 comes at number 10, therefore for any Product Name (say ADAAA or ADAAV), their corresponding Product Trans should come in that position only.
No, we will have only above Product types (00000002222 won't be there)


Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7
ADAAA 95-05929 95-05961 95-23385 95-06462 95-23414 95-06219 95-05988...so on
ADAAB 95-05929 95-05961 95-23385 95-06462 95-23414 95-06219 95-05988...so on
ADAAV                                                       95-05988...so on
ADAEH 95-05929
ADAEI   {95-06737 in 13th position as Product Type for this is 00000008100 only}

Thanks,
Mahesh
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
PostPosted: Thu Jul 07, 2016 1:31 pm    Post subject: Reply with quote
Mahesh_Gandla,

Use the following DFSORT/ICETOOL JCL which will give you the desired results. I assumed your data is already sorted on the Product Name. If it is not then all you need is to remove OPTION COPY statement in CTL1CNTL

Code:

//STEP0100 EXEC PGM=ICETOOL                                           
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//IN       DD DISP=SHR,DSN=Your Input FB LRECL 70 byte file                                     
//OUT      DD SYSOUT=*                                                 
//TOOLIN   DD *                                                       
  SPLICE FROM(IN) TO(OUT) ON(1,5,CH) WITHANY USING(CTL1)          -   
  KEEPNODUPS WITH(007,09) WITH(016,09) WITH(025,09) WITH(034,09)  -   
             WITH(043,09) WITH(052,09) WITH(061,09) WITH(070,09)  -   
             WITH(079,09) WITH(088,09) WITH(097,09) WITH(106,09)  -   
             WITH(115,09)                                             
//*                                                                   
//CTL1CNTL DD *                                                       
  OPTION COPY                                                         
  INREC IFOUTLEN=123,                                                 
  IFTHEN=(WHEN=INIT,BUILD=(1,5,124:8,8,133:18,11)),                   
  IFTHEN=(WHEN=(133,11,CH,EQ,C'00000001100'),OVERLAY=(007:124,09)),   
  IFTHEN=(WHEN=(133,11,CH,EQ,C'00000001200'),OVERLAY=(016:124,09)),   
  IFTHEN=(WHEN=(133,11,CH,EQ,C'00000001300'),OVERLAY=(025:124,09)),   
  IFTHEN=(WHEN=(133,11,CH,EQ,C'00000001301'),OVERLAY=(034:124,09)),   
  IFTHEN=(WHEN=(133,11,CH,EQ,C'00000001600'),OVERLAY=(043:124,09)),   
  IFTHEN=(WHEN=(133,11,CH,EQ,C'00000001630'),OVERLAY=(052:124,09)),   
  IFTHEN=(WHEN=(133,11,CH,EQ,C'00000004100'),OVERLAY=(061:124,09)),   
  IFTHEN=(WHEN=(133,11,CH,EQ,C'00000004195'),OVERLAY=(070:124,09)),   
  IFTHEN=(WHEN=(133,11,CH,EQ,C'00000004400'),OVERLAY=(079:124,09)),   
  IFTHEN=(WHEN=(133,11,CH,EQ,C'00000004800'),OVERLAY=(088:124,09)),   
  IFTHEN=(WHEN=(133,11,CH,EQ,C'00000006400'),OVERLAY=(097:124,09)),   
  IFTHEN=(WHEN=(133,11,CH,EQ,C'00000006410'),OVERLAY=(106:124,09)),   
  IFTHEN=(WHEN=(133,11,CH,EQ,C'00000008100'),OVERLAY=(115:124,09))     
//*

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group