|
View previous topic :: View next topic
|
| Author |
Message |
trushant.w
New User
.jpg)
Joined: 22 Sep 2013
Posts: 81
Location: pune india
|
|
|
|
I have a input file where there are two types of records
1. Records from position 1-30 (rest are blank)
2. Records from position 41-80 (rest are blank)
Now there is comman key in both these two type of records of 10 bytes
11-20 (in first type of record)
51-60 (in second type of records)
I want the output as below
1. if the key is match, i want consolidated record of two records means from pos 1-80 where 11-20 and 51-60 will have same value as key is matched
2. if the key is not matching then i want to retain both the records as it is
Input
| Code: |
54545022 JOHNS 12 54775
54545022 FACT005 89 88545
54545022 GTOP 88 54566
STREET 15 MILLER5589 652 5398566
STREET 15 FACT005 89 652 5398FSD
STREET 15 GTOP 88 652 5398TRE |
Output
| Code: |
54545022 JOHNS 12 54775
54545022 FACT005 89 88545 STREET 15 FACT005 89 652 5398FSD
54545022 GTOP 88 54566 STREET 15 GTOP 88 652 5398TRE
STREET 15 MILLER5589 652 5398566 |
Here FACT005 89,GTOP 88 are matching keys
As the volume of records is huge i need to do it in single step.
Hope i am clear with reuirement |
|
| Back to top |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
As the volume of records is huge, you need to do it efficiently. Efficiently dictates how many steps, not the other way around.
JOINKEYS with the same DSN as both inputs
In JNF1CNTL create a record only containing the "left side" data.
In JNF2CNTL create a record only containing the "right side" data.
JOIN UNPAIRED,F1,F2
REFORMAT with F1:entire-record,F2:entire-record
That's about it.
Unless you can have duplicate keys in either or both sets of data... |
|
| Back to top |
|
|
trushant.w
New User
Joined: 22 Sep 2013
Posts: 81
Location: pune india
|
|
|
|
| But unpaired will retain only unmatch records but i want both match and unmatch records |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Well, instead of asserting that, why don't you read the manual? If you don't believe the manual, why don't you experiment? If you then sill feel the need to make the same objection, by all means do so. |
|
| Back to top |
|
|
trushant.w
New User
Joined: 22 Sep 2013
Posts: 81
Location: pune india
|
|
|
|
for the same requirement mentioned above i am using the same file in
SORTJNF1,SORTJNF2 and keys are starting at position 34 and 75 both are of 10 bytes.Keys are alphanumeric
i am using below sort card
| Code: |
JOINKEYS FILE=F1,FIELDS=(34,10,A)
JOINKEYS FILE=F2,FIELDS=(75,10,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,45,F2:47,58)
SORT FIELDS=COPY |
But in output i am getting only spaces,but i want a consolidated record for both match and unmatch keys |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Quote: |
| where 11-20 and 51-60 will have same value as key is matched |
| Quote: |
JOINKEYS FILE=F1,FIELDS=(34,10,A)
JOINKEYS FILE=F2,FIELDS=(75,10,A) |
|
|
| Back to top |
|
|
trushant.w
New User
Joined: 22 Sep 2013
Posts: 81
Location: pune india
|
|
|
|
Hi Bill
Sorry for confusion this file has different layout but the requirement is same
keys start from position 34 and 75 |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
What happened to your JNFnCTNL files? You have probably included blank data for all the blank keys, which will match before the match you want.
When creating the "left side" data, you remove all the keys which are blank. When creating the "right side" data you do the same. You need to have the JOINKEYS sort the data for the match, so the sort will be more efficient the less data you give it. Cut down the records, as I suggested. |
|
| Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
It can be done in a single step itself instead of joinkeys. Just use an IFTHEN validating the space for record type 1 and place the key at the end of the record.
Now sort on the key value at the end and use WHEN=GROUP on OUTREC to push the value for matched records. Use OUTFIL sections with Trailer3 to get just 1 record per key. |
|
| Back to top |
|
|
trushant.w
New User
Joined: 22 Sep 2013
Posts: 81
Location: pune india
|
|
|
|
Hi Skolusu
Thanks for reply
I am unable to understand WHEN=GROUP logic |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
There are several examples in previous topics.
Suggest you use the SEARCH facility (in the blue line near the top) to locate some. Most have sample data and the sort code to do this. |
|
| Back to top |
|
|
|
|
