View previous topic :: View next topic
|
Author |
Message |
Squeak6
New User
Joined: 04 Aug 2005 Posts: 36
|
|
|
|
** I had to create a new topic ~ fresh start **
I apologize if I was not clear earlier.. I have 2 files with the same layout ( file A & file B) which I’d like to combine into 1 ( file C ).. I need to know how to add the fields below ( not all together )
EX. Add overtime hours from both files creating a new combined overtime hours from both files.. do this for each one of the packed files below.. all while retaining the same layout.
Both files have a length of 120.. I included the position and length of each filed.
Code: |
FIELD NAME START END LEN
05 MO-OVERTIME-HRS PIC S9(7)V9(2) COMP-3. 051 055 005
05 MO-OVERTIME-AMT PIC S9(9)V9(2) COMP-3. 056 061 006
05 MO-OVERTIME-RETRO-AMT PIC S9(9)V9(2) COMP-3. 062 067 006
05 MO-YTD-OVT-HRS PIC S9(9)V9(2) COMP-3. 068 073 006
05 MO-YTD-OVT-AMT PIC S9(11)V9(2) COMP-3 074 080 007
05 MO-YTD-OVT-RETRO-AMT PIC S9(11)V9(2) COMP-3 081 087 007 |
|
|
Back to top |
|
|
elango_K
New User
Joined: 18 Aug 2011 Posts: 44 Location: India
|
|
|
|
What is the key on these files?
How do we know which record has to be added to ehich record??? |
|
Back to top |
|
|
Squeak6
New User
Joined: 04 Aug 2005 Posts: 36
|
|
|
|
the key is SSN
Code: |
FIELD NAME START END LEN
05 MO-SSN PIC 9(9). 016 024 009 |
** I've done something like this before using:
INREC OVERLAY=
SUM FIELDS=
OUTREC OVERLAY=
but I've never had more than one filed and never had packed data ** |
|
Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
|
|
|
|
Squeak6,
Did you even try to answer the questions I asked previously? What is the point of opening another topic with no explanation? How hard is to answer a simple q?
What do you do with unmatched records from file 1 and file 2?
If you need help, then answer ALL the questions and post a sample data of both input and output. |
|
Back to top |
|
|
Squeak6
New User
Joined: 04 Aug 2005 Posts: 36
|
|
|
|
the reason I did not show an example of the file is because the data is packed.. packed data would not have shown.. I felt by showing the file layout would be the best approach..
was I wrong? ( please advise.. ) |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
Squeak6,
You can show packed data in the same way you show character data - just indicate it's packed. For example:
I have 2-byte PD fields in positions 1-3 and 6-8 with values like this:
Code: |
+123 -989
-002 +005
|
It's still not clear what you want to do. If you want to get the totals for all of your PD values, you can use TRAILER1 with TOT for PD. If you want to get the totals for each key value, you can use SUM with PD, or SECTIONS with TRAILER with TOT for PD. If you want more specific help, you need to clarify what you want to do:
Please show an example of the records in each input file (relevant fields only) and what you expect for output. Explain the "rules" for getting from input to output. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input files. If file1 can have duplicates within it, show that in your example. If file2 can have duplicates within it, show that in your example. |
|
Back to top |
|
|
Squeak6
New User
Joined: 04 Aug 2005 Posts: 36
|
|
|
|
File A ( file B is exactly the same.. the amount fields will have different amounts..)
position 1 - 72
Code: |
360aaaaaaaabbbb123456789jones, JORGE L 000001 Í i * °
FFF888888888888FFFFFFFFF9998A64DDDCC4D444444FFFFFF0007000084500000000019
36011111111222212345678916552B016975030000000000010025C00190C00000C00060 |
Positions 73-120
Code: |
Äq à 00101016AC00022618
000016900000262FFFFFFFFCCFFFFFFFF000000000000000
C000138C000006C001010161300022618000000000000000 |
below are the like fields I would like to add together.
Code: |
MO-OVERTIME-HRS PIC S9(7)V9(2) COMP-3. 051 055 005
MO-OVERTIME-AMT PIC S9(9)V9(2) COMP-3. 056 061 006
MO-OVERTIME-RETRO-AMT PIC S9(9)V9(2) COMP-3. 062 067 006
MO-YTD-OVT-HRS PIC S9(9)V9(2) COMP-3. 068 073 006
MO-YTD-OVT-AMT PIC S9(11)V9(2) COMP-3 074 080 007
MO-YTD-OVT-RETRO-AMT PIC S9(11)V9(2) COMP-3 081 087 007 |
I want to add the like fields in above layout from A & B . All other fieds will just carry over from file A.
layout of both A&B
Code: |
FIELD NAME START END LEN
01 MO-INDEX-OVERTIME-REC. 001 120 120
05 MO-INDEX-KEY. 001 024 024
10 MO-DEPARTMENT PIC 9(3). 001 003 003
10 MO-INDEX-CODE PIC X(12). 004 015 012
10 MO-SSN PIC 9(9). 016 024 009
10 MO-SSN-R 016 024 009
REDEFINES MO-SSN.
15 MO-SSN-1-3 PIC 9(3). 016 018 003
15 MO-SSN-4-5 PIC 9(2). 019 020 002
15 MO-SSN-6-9 PIC 9(4). 021 024 004
05 MO-NAME PIC X(20). 025 044 020
05 MO-OCCP-CODE PIC 9(6). 045 050 006
05 MO-OVERTIME-HRS PIC S9(7)V9(2) COMP-3. 051 055 005
05 MO-OVERTIME-AMT PIC S9(9)V9(2) COMP-3. 056 061 006
05 MO-OVERTIME-RETRO-AMT PIC S9(9)V9(2) COMP-3. 062 067 006
05 MO-YTD-OVT-HRS PIC S9(9)V9(2) COMP-3. 068 073 006
05 MO-YTD-OVT-AMT PIC S9(11)V9(2) COMP-3 074 080 007
05 MO-YTD-OVT-RETRO-AMT PIC S9(11)V9(2) COMP-3 081 087 007
05 MO-DDL PIC 9(8). 088 095 008
05 MO-DDL-R 088 095 008
REDEFINES MO-DDL.
10 MO-DDL-1-3 PIC 9(3). 088 090 003
10 MO-DDL-4-5 PIC 9(2). 091 092 002
10 MO-DDL-6-8 PIC 9(3). 093 095 003
05 MO-EMPL-STATUS PIC X(2). 096 097 002
05 MO-EMPID PIC 9(08). 098 105 008
05 FILLER PIC X(15). 106 120 015 |
( somethimes its hard to type what you're trying to do.. ) |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
This is like pulling teeth. I wanted to see input values in multiple records and the expected output values like this:
Code: |
Input 1:
AAAA 10 20
AAAA 20 05
BBBB 05 18
CCCC 08 06
Input 2:
AAAA 15 05
BBBB 09 22
|
Expected output?
I still don't know what you want to do.
For the fields to be added:
Do you want to get ONE total for ALL of the records, or do you want to get ONE total for EACH key value? If the latter, what is the position, length and format of the key and are the keys in each input file already in sorted order (as implied by your use of the term MERGE)? |
|
Back to top |
|
|
Squeak6
New User
Joined: 04 Aug 2005 Posts: 36
|
|
|
|
I hope this helps.. (Frank I wish u had a help line # )
Code: |
FILE A
123456789 ABCDEFGHI100 -000000010 00
987654321 ABCDEFGHI101 -000000020 00
123456123 ABCDEFGHI102 -000000030 00
FILE B
123456789 XYZFDJFNI201 -000000010 00
987654321 XYZFWFNKI301 -000000020 00
FILE C
123456789 ABCDEFGHI100 -000000020 00
987654321 ABCDEFGHI101 -000000040 00
123456123 ABCDEFGHI102 -000000060 00 |
|
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
I do not see how you get -60 for the last line - surely it is -30 as you only have one record for 123456123 |
|
Back to top |
|
|
Squeak6
New User
Joined: 04 Aug 2005 Posts: 36
|
|
|
|
Nic Clouston wrote: |
I do not see how you get -60 for the last line - surely it is -30 as you only have one record for 123456123 |
correct..!!! |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
Wow, you really are bad at describing what you want.
Assuming you want to add the PD fields for each key and that the keys are not in sorted order:
Just concatenate the two files as SORTIN DD, SORT on the key, and SUM on the PD fields you want totalled, e.g.
Code: |
...
//SORTIN DD DSN=... input file1
// DD DSN=... input file2
//SORTOUT DD DSN=... output file
//SYSIN DD *
OPTION EQUALS
SORT FIELDS=(p,m,f,A)
SUM FIELDS=(p1,m1,PD,p2,m2,PD,...)
/*
|
This will give you one record for each key with the totals for that key. The output records will be in sorted order by the key.
EQUALS will ensure that the other fields from the first record with each key are kept.
If you really want the records in some other order (as implied by your example), you'll need to explain what order you want them in. |
|
Back to top |
|
|
Squeak6
New User
Joined: 04 Aug 2005 Posts: 36
|
|
|
|
I got it to work..( checking totals just to make sure.. )
I was missing the options equals.. ( when I tried on my own.. )
Code: |
*******************************************************************
* THIS CODE WILL COMBINED DUPLICATE AMOUNTS FROM 2 FILES.. *
*******************************************************************
OPTION EQUALS
SORT FIELDS=(16,9,CH,A)
SUM FIELDS=(51,5,PD,56,6,PD,62,6,PD,68,6,PD,74,7,PD,81,7,PD) |
I had 4 records.. 2 on each test file.. ended up with 2.. and it took the informaiton I needed from the 1st file.. |
|
Back to top |
|
|
Squeak6
New User
Joined: 04 Aug 2005 Posts: 36
|
|
|
|
(( Thank you.. )) |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
Well, I'm glad we finally got that straightened out. |
|
Back to top |
|
|
|