Posted: Wed Dec 14, 2005 9:20 am Post subject: Problem with ICETOOL Compare files
I have a job which used to work,with Frank's help, and now it does not after I made some changes. This job actually saves me a lot of programming, so I want to make this work. Input and output files have LRECL=5000 and FB, I have 3 keys and have the following positions in my file ADFLAG (pos 98,32),ADJID (164,32) The Amount can be upto 25 bytes and not 8 as shown in the job and the keys can be maximum 32 bytes
Note:
------
A STATUSID of SETTLED in file2 should always produce a second record
with a STATUSID of BOOKED
Rule 3,5 and 6 works with the below job. I am going in circles trying to fix the other rules.
FILE1
Code:
AMOUNT ADJFLAG EVMADJID STATUSID Rule
----------------------------------------------------
2000 N 10002 OPEN 1
3000 N 10002 OPEN 2
1500 N 10002 OPEN 3
4500 N 20002 OPEN 4
300 N 10002 BOOKED 5
300 N 10002 BOOKED 6
100 N 3100A OPEN 7
FILE2
Code:
AMOUNT ADJFLAG EVMADJID STATUSID Rule
--------------------------------------------------------
2000 N 10002 OPEN 1
3000 N 10002A OPEN 2
1500 N 10002 BOOKED 3
4500 N 20002 SETTLED 4
300 N 10002 SETTLED 5
250 N 10002 SETTLED 6
900 N 67182 SETTLED 7
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Wed Dec 14, 2005 11:00 am Post subject:
Quote:
Rule 3,5 and 6 works with the below job. I am going in circles trying to fix the other rules.
You haven't said what the rules are.
I don't know how you expect anyone to help you with this based on the limited information you've given in this post. _________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
I know it is difficult to help without any proper specifications. I hope I got
it right this time.
What I am trying to do is compare file1 with file2, and produce an output
file based on the rules mentioned below
File1
Code:
AMOUNT ADJFLAG EVMADJID STATUSID
----------------------------------------------------
2000 N 10002 OPEN 1
3000 N 10002 OPEN 2
1500 N 10002 OPEN 3
4500 N 20002 OPEN 4
300 N 10002 BOOKED 5
300 N 10002 BOOKED 6
100 N 3100A OPEN 7
File 2
Code:
AMOUNT ADJFLAG EVMADJID STATUSID
------------------------------------------------------
2000 N 10002 OPEN 1
3000 N 10002A OPEN 2
1500 N 10002 BOOKED 3
4500 N 20002 SETTLED 4
300 N 10002 SETTLED 5
250 N 10002 SETTLED 6
900 N 67182 SETTLED 7
Rules:
-------
1). File1 and File2 same keys, Amount and status duplicate, nothing in
out file
2). File2 record not found in file1, so file2 record in out file
3). File1 and file2 have the same keys but with a status of OPEN in File1
and BOOKED in file2. Out file should have the file1 record with the
Amount reversed and the record from file2. (This rule works with my
job)
4). File1 and file2 have the same keys but with a Status of OPEN in file1
and SETTLED in file2. Out file should have the record from file1 with
the Amount reversed,record from file2 with status SETTLED, and again
the record from file2 but now with the Status BOOKED. A status of
SETTLED in file2 should always produce another record with status
BOOKED.
5). File1 and file2 have the same keys but with a Status of BOOKED in
file1 and SETTLED in file2 and with the same Amount. Out file should
have the record from file1 with the Amount reversed, record from
file2 with status SETTLED, and again the record from file2 but now
with the Status BOOKED.
If the reversed amount from file1, and the amount from file2 with
the same status = 0, in this case only SETTLED record from file2
in out file.
(This rule works with my job)
6).File1 and file2 have the same keys but with a Status of BOOKED in
file1 and SETTLED in file2 and with different Amount. Out file should
have the record from file1 with the Amount reversed, record from
file2 with SETTLED and and again the record from file2 but now
with the Status BOOKED. (This rule works with my job)
7). Since the keys are different only record from file2 in out file and since
it's SETTLED status in file2,generate another record with the Status
BOOKED
My job produces the proper results for rules 3,5 and 6. I am having difficulties with the other rules. Rule1 for example, should'nt KEEPNODUPS remove duplicate records?
Here is what the out file should look based on the above example files
and rules
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Wed Dec 14, 2005 3:42 pm Post subject:
Sorry, I won't have time to look at this for a while. I'm in the middle of packing to move to a new site and I'll be on vacation for the next two weeks. If I get a chance, I'll take a look, but no promises. Maybe somebody else can help. _________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Thu Dec 15, 2005 7:53 pm Post subject:
Started looking at this but couldn't figure out the positions and lengths of some fields and some other stuff.
Please list the position and length of:
AMOUNT
ADJFLAG
EVMADJID
STATUSID
Are all of the starting positions the same in FILE1 and FILE2?
Quote:
Rule1 for example, should'nt KEEPNODUPS remove duplicate records?
KEEPNODUPS says you want to keep any records that don't have a match. Without KEEPNODUPS, the non-matching records are deleted. For matching records (dups), the base and overlay records are spliced together according to the parameters you specify (ON, WITHALL, WITHEACH, WITH).
You say "I have 3 keys". Which fields are the 3 keys?
You say "File1 and File2 same keys, Amount and status duplicate". How does this correspond to the 3 keys? If Amount and status are not keys, then there would only be 2 fields left as keys. If Amount or status is one of the keys, why did you say same keys and then list those two?
How do these rules relate to the rules you had for the original job I gave you? What's different this time? _________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Position and length
----------------------
AMOUNT 1,25
ADJFLAG 98,32
EVMADJID 164,32
STATUSID 230,32
- The positions are the same in File1 and File2.
- I have 2 keys(ADJFLAG and EVMADJID) and not 3 keys. I meant to write 2 keys.
In the original job ,Rule 4 and 7 were not tested , when file1 had a OPEN
status and file2 with SETTLED. I tried to modify my job to get rule1,2,4 and 7 to work, but I did not get the expected result,so I had to ask for help. What is different is that the Amount is now 25 bytes (1,25). I was testing with 8 bytes Amount, trying to get all my rules to work and later
adapt my job for 25 bytes Amount.
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Fri Dec 16, 2005 12:13 pm Post subject:
I just realized that you have duplicate keys in each file (10002). SPLICE is not going to be able to tell which of these keys in file1 matches up with which of these keys in file2 unless you also want to match up the record numbers (e.g. file1 record 1 with file2 record 1, file1 record 2 with file2 record 2, etc), so you'd essentially have the record number as the first key. Is that what you want? _________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Using record number as the key is not really what I want as the records to be spliced will not be in the same order in the files
I tried testing rule1 with just 1 record each, in file1 and file2 like below
Code:
AMOUNT ADJFLAG EVMADJID STATUSID
----------------------------------------------------
2000 N 10002 OPEN (file1)
2000 N 10002 OPEN (file2)
and I got the follwing result in my out file,and I do not want anything in the out file (duplicates)
Code:
AMOUNT ADJFLAG EVMADJID STATUSID
----------------------------------------------------
-2000 N 10002 OPEN (from file1)
2000 N 10002 OPEN (from file2)
Since the keys match,should'nt KEEPNODUPS remove the records?
Frank, I really don't feel very comfortable bothering you,when you are on vacation. I am grateful that,you still find the time to look into this problem. I could also wait till you get back.
Thanks again
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Sat Dec 17, 2005 11:28 am Post subject:
Does each file have duplicate records within it as shown in your previous example (e.g. several records in each file with N/10002)? If so, and you're not matching record-by-record, then you won't be able to use SPLICE to do what you want because it can't tell which dup in file1 is supposed to match up with which dup in file2.
Quote:
Since the keys match,should'nt KEEPNODUPS remove the records?
KEEPNODUPS keeps records without a match - it doesn't remove any records. A record is removed when two records that match are SPLICEd together.
So you can read for yourself what each of the SPLICE parameters does. _________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
The keys will be unique in each file. My examples(rules) are all individual test cases,I find is easier to test it this way. That is the reason for duplicate keys.
The rules 3,4,5,6 work when the keys are the same but fails when the keys in file2 are different than File1. If the keys in file2 are not found in file1, then only the, file2 record should be in out file, an additional record
in out file, if the file2 has a status of SETTLED. This works well in the below
Job.
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
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
