Output File: LRECL=58
----+----1----+----2----+----3----+----4----+----5----+---
011114111415024A 310? 0AE36913142 1210Y7008275524
021114111415024A 310` |0AE36928142 1210Y700827008770058
031114111415024A 310` |0AE36924142 1210N
021114111415024A 310` |0AE36925142 1210Y700827008770058[/img]
[/img]
Can SPLICE option in ICETOOL can compare the file1 with file2 in the three keys from(given in red,blue and green). when the match is found The values from 11 thru 26 in file 2 has to be appeneded in output file from 43 to 58.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
For SPLICE we need to sort on your keys which would put the output records in key order. Since you want your output records in the original order of the input records (at least that's what you show in your example), we need to add a sequence number to each input record so we can sort the SPLICEd records on that sequence number to get them back into the order you want. The extra SORT operator sorts on the added sequence number and then removes it.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
I based my solution on the input and output example you showed. If you need other variations, show me a better example of the input and output records including all of the variations and I'll show you how to do what you want.
Joined: 08 Jun 2006 Posts: 45 Location: Trumbull, CT
My first input file had around 8000records.
The second input file had some 1500 records..
My requirement was to put all the 8000records as it is in the output file and the searched values if found had to be appended to those 8000 resepective record, or else leave the reocrd as it is.
But when i used KEEPNODUPS i got as much as around 9500records including the including the records that were used for matching I think...
But i want only those 8000 records from the file 1. HEnce do i need to use another sort step or can this be done in the same step of SPLICE?
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
You keep changing the requirement! I said
Quote:
I based my solution on the input and output example you showed. If you need other variations, show me a better example of the input and output records including all of the variations and I'll show you how to do what you want.
You first showed me an example where every record in file1 had a match in file2. I gave you the solution for that. Then you showed me an example where records in file1 did not have a match in file2 and I gave you the solution for that.
Are you now saying that records in file2 do not have a match in file1? Or do you have duplicates in file2? Or some other combination?
In any case, you need to show me a good example of the input and output for all of your variations. I can only go by what you tell me.
File 1(around 8000 records)
has duplicates(if we consider only the positions that are to be matched)
File 2(around 5000 records)
has no duplicates(by any means)
The key I mentioned remains the same.
first 2 position from file 1 has to be matched with 6th and 7th position in file 2 and
12th to 16th position from file 1 has to be matched with 1st to 5th position in file 2 and
18th to 20th position from file 1 has to be matched with 8th to 10th position in file 2
Search on the key for first record of file 1 with all the records of file 2.
If match found then append the characters of the matched record from 11th to 26th of file 2 to 43rd to 58th position of file 1 and copy it to the output file.
If no match is found then jus copy the record from file 1 to output file.
Search on the key for second record of file 1 with file 2 and so on...
The fifth and sixth record in file 1 has to be copied to output file as it is, since there is no match for them in file 2.
The 2nd and fourth record has same keys but different data in other position. For this case I need to jus append the found values. as in the 2nd and fourth record in output file.
The last 3 records from file 2 has no match with the file 1. Hence they should not be copied to the output file.
I think I have given a clear view of my requirement. Can you please look into this and suggest me a way to do it? It will be really helpful...
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
No, I meant to use WITH(59,9) to get the sequence number and one byte of the two byte id so we would get VB for matched records, BB for file1 only records and VV for file2 only records. With WITH(59,10) you'll get BB for file1 only records and VV for file2 only or matched records. For your particular case, either way would work since you only want the BB records. But in the general case of matched records, file1 records and file2 records, you'd need WITH(59,9) rather than WITH(59,10).
