Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Merge & Sum from two files

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
atulxp

New User


Joined: 27 Nov 2005
Posts: 15
Location: Phoenix

PostPosted: Tue Jun 14, 2011 5:03 am    Post subject: Merge & Sum from two files
Reply with quote

Hi,

Could anyone help me with following problem?

I have two files, File 1 is transaction file & file 2 is master file. I have to prepare next master file.

File 1 Data elements
Field 1:- Customer Code X(4)
Field 2:- Transaction Type X(1)
Field 3:- Amount 9(2).99


Code:


1111     A  10.00
1111     B  32.30
2222     B  13.21


Master file Data Elements

Field 1:- Customer Code X(4)
Field 2:- Transaction Type X(1)
Field 3:- Daily Amount 9(2).99
Field 4:- Month to Date Amount 9(2).99
Field 5:- Year to date Amount 9(3).99

Code:

1111  A  30.00       50.00        100.00
3333  D  10.10       30.00        200.00



I want output like
Code:

1111   A  10.00           60.00          110.00
1111   B  32.30           32.30          032.30
2222   B  13.21           13.21          013.21
3333   D  10.10           30.00          200.00


Number of elements could be dynamic so I cant hard code for only three customers & there could A to K types of transactions.

Any help will be appriciated.
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Jun 14, 2011 5:26 am    Post subject:
Reply with quote

You can use a DFSORT JOINKEYS job like the following to do what you asked for. I assumed your fields are contiguous as described by your definitions rather than with spaces in between as shown in your example, but adjust as needed:

Code:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD *
1111A10.00
1111B32.30
2222B13.21
/*
//IN2 DD *
1111A30.0050.00100.00
3333D10.1030.00200.00
/*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(1,5,A)
  JOINKEYS F2=IN2,FIELDS=(1,5,A)
  JOIN UNPAIRED,F1,F2
  REFORMAT FIELDS=(F1:1,10,F2:1,21,?)
  OPTION COPY
  OPTION COPY
  OUTREC IFTHEN=(WHEN=(32,1,CH,EQ,C'1'),
    BUILD=(1,10,6,5,C'0',6,5)),
   IFTHEN=(WHEN=(32,1,CH,EQ,C'2'),
    BUILD=(11,21)),
   IFTHEN=(WHEN=(32,1,CH,EQ,C'B'),
    BUILD=(1,10,6,5,UFF,ADD,21,5,UFF,EDIT=(TT.TT),
       6,5,UFF,ADD,26,6,UFF,EDIT=(TTT.TT)))
/*


SORTOUT would have:

Code:

1111A10.0060.00110.00   
1111B32.3032.30032.30   
2222B13.2113.21013.21   
3333D10.1030.00200.00   
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7251

PostPosted: Tue Jun 14, 2011 12:18 pm    Post subject: Re: Merge & Sum from two files
Reply with quote

atulxp wrote:


[...]

Master file Data Elements

Field 1:- Customer Code X(4)
Field 2:- Transaction Type X(1)
Field 3:- Daily Amount 9(2).99
Field 4:- Month to Date Amount 9(2).99
Field 5:- Year to date Amount 9(3).99

[...]



I have never seen amounts defined as being so small. A daily amount of little-over 3 on average will strain your month-to-date and year-to-date.

Why are you storing them as "edited" fields (physical not implied decimal place)?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jun 14, 2011 8:35 pm    Post subject:
Reply with quote

Hello,

Possibly just a sample to be replaced with the actual lengths after testing is complete.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7251

PostPosted: Tue Jun 14, 2011 8:58 pm    Post subject: Reply to: Merge & Sum from two files
Reply with quote

Well, I suppose if they want to double-up on the testing, it's OK with me.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 15 Thu Dec 01, 2016 8:28 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm
No new posts Adding records from two files into on... shiitiizz SYNCSORT 4 Mon Sep 19, 2016 8:41 pm
No new posts Icetool or Joinkeys example to compar... ishant chauhan DFSORT/ICETOOL 21 Sat Aug 20, 2016 2:40 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us