|
View previous topic :: View next topic
|
| Author |
Message |
rajendra kalepu
New User
Joined: 29 Jul 2011
Posts: 21
Location: India
|
|
|
|
Hi Team,
I am facing a problem when working with joins. Let me tell my requirement first
Requirement: To compare two files based on a Key and add the code present in last 2 columns of one file to other file
Here is the code which i am using
LRECL of F1 is 569
LRECL of F2 is 355
| Code: |
JOINKEYS FILE=F1,FIELDS=(9,7,A) --> First file Key is in 9th position
JOINKEYS FILE=F2,FIELDS=(12,7,A) --> Second file Key is in 12th Position
JOIN UNPAIRED,F1 --> Wanted only Paired records from F1 as well as Unpaired from F1
REFORMAT FIELDS=(F1:1,569,F2:1,355),FILL=X'FF'
SORT FIELDS=(1,567,CH,A) --> Trying to eliminate Duplicates if any
SUM FIELDS=NONE
INREC IFTHEN=(WHEN=((1,1,BI,NE,X'FF'),AND,(9,7,CH,EQ,581,7,CH)),
BUILD=(1:1,567,568:923,2)),--> adding the last 2 digits from F2 to F1 if a match is found
IFTHEN=(WHEN=NONE,
BUILD=(1:1,569))--> Write the record as it is from F1 when no match is found |
Now i had a scenario where F2 is having 2 entries for a particular Key (say abcdefg) One record having code in last 2 columns and another one having spaces
After matching is done i am unable to retrieve the code to the last 2 columns of F1, Since it is picking the second record of F2 where the last 2 columns is blank
Simple example for better understanding:
F1
| Code: |
ab spaces
cd spaces
ef spaces
|
F2
| Code: |
ab 01
ab spaces
cd 02
ef 03 |
After matching i should get the following result
But My output file is looking like
| Code: |
ab spaces --> i want the code to be added
cd 02
ef 03 |
Note: F1 is having a Unique record for Particular Key
F2 may have more than one record for a particular key
Its like One to Many mapping
Please help me in this requirement. Correct me if i was going wrong some where
Code'd |
|
| Back to top |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Is that the full range of possible duplicates on file two? If not, give a representative sample please. |
|
| Back to top |
|
|
rajendra kalepu
New User
Joined: 29 Jul 2011
Posts: 21
Location: India
|
|
|
|
Hi Bill
F1 will be having a unique record for a particular Key
Where as F2 will have more than One record for a particular Key
I tried to eliminate duplicates because there is a chance of multiple matching
results when we are trying to compare
I mean to say we may get 2 matches in File2 when we are comparing a single record in file1
Ultimate Goal: My ultimate goal is to pick the last 2 columns of File2 and add it in file 1 if the key matches other wise write the record as it is
Please let me know if you need more information |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Bill Woodger wrote: |
| Is that the full range of possible duplicates on file two? If not, give a representative sample please. |
What I want is some sample input showing some combinations od duplicates and what you want for your output for each example.
Why are you sorting on a huge part of the REFORMAT record? Or why do you have a different key for your Main Task SORT than for your JOINKEYS? |
|
| Back to top |
|
|
rajendra kalepu
New User
Joined: 29 Jul 2011
Posts: 21
Location: India
|
|
|
|
Hi Bill,
I was unable to post the original data since the record length is too large
Here is an example which you can understand better
Assume key as first 3 columns in file1 i.e abc, efg etc.,
So now i am trying to match the 2 files
File 1 :
| Code: |
abcalpha 123 spaces
efgaplha 456 spaces
hijalpha 789 spaces |
File 2 :
| Code: |
??? abcalpha 01
+++ abcbetaa 01
--- abcgamma spaces |
Since im giving unpaired F1 my result will be paired records as well as unpaired records in F1
| Code: |
abcalpha 123 01
abcalplha 123 01
abcalpha 123 spaces
efgaplha 456 spaces
hijalpha 789 spaces |
Now im trying to eliminate duplicates by sorting just upto (abcaplha 123)
so my result would be :
| Code: |
abcalpha 123 01
efgaplha 456 spaces
hijalpha 789 spaces |
But unfortunately my result is like
| Code: |
abcalpha 123 spaces
efgaplha 456 spaces
hijalpha 789 spaces |
Please correct me if i am trying to do some thing wrong |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
OK. I still have no clue of what you actually want. You don't want space? Is that it?
If so, put OMIT COND=(start,2,CH,EQ,C' ') in JNF2CNTL.
If you only want one record for your output, and it doesn't matter which because they both have 01, use IFTHEN=(WHEN=GROUP for your key, PUSH a sequence number, and on OUTFIL INCLUDE=(sequence number position,length,ZD,EQ,1) or similar.
SORTing just so you can use SUM FIELDS=NONE is wasteful of resources. |
|
| Back to top |
|
|
rajendra kalepu
New User
Joined: 29 Jul 2011
Posts: 21
Location: India
|
|
|
|
Thanks Bill,
I will try that today and let you know if i have any questions |
|
| Back to top |
|
|
rajendra kalepu
New User
Joined: 29 Jul 2011
Posts: 21
Location: India
|
|
|
|
Hi Bill,
Today i tried with the logic provided
Card used:
| Code: |
JOINKEYS FILE=F1,FIELDS=(9,7,A)
JOINKEYS FILE=F2,FIELDS=(12,7,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,569,F2:1,355),FILL=X'FF'
SORT FIELDS=COPY
OUTREC IFTHEN=(WHEN=GROUP,BEGIN=((1,1,BI,NE,X'FF'),
AND,(9,7,CH,EQ,581,7,CH)),
PUSH=(568:921,2)), --> trying to group the matched records and push the last 2 columns from file 2 to file 1
IFTHEN=(WHEN=GROUP,BEGIN=((1,1,BI,NE,X'FF'),
AND,(570,1,BI,EQ,X'FF')),
PUSH=(1:1,569))-->when there is no match in file 2 found write the file 1 record as it is
OUTFIL BUILD=(1,569)
|
But unfortunately i was unable to get the result
Here is an simple example for better understanding. Assume the key is first 2 charcters i.e (ad, ef, and hi respectively)
File1:
| Code: |
abcd spaces
efgh spaces
hijk spaces |
File2:
| Code: |
abza 01
abzb 01
abzc 01
abzd spaces
efza 02
efzb 02 |
Output expected:
| Code: |
abcd 01 --> for one ab record in file 1 there are 4 matches in file2
abcd 01
abcd 01
abcd spaces
efgh 02--> for one ef record in file1 there are 2 matches in file2
efgh 02
hijk spaces --> no matches hence writing record as it is |
Final output should be like:
| Code: |
abcd 01
efgh 02
hijk spaces |
My ultimate goal is to update the file 1 with the codes present in file 2 in the last 2 columns for matching key
If there is no match then write the record as it is
Main problem is occurring in following cases
1.When the matched record is having a value in last 2 columns
2.When the same matched record is also having spaces in last 2 columns
In such scenario i was getting spaces in my output file
Assume
File1 - 20 records
File2 - 30 records
Matching in both - 10 records
Now my output file count should be - 20 records (same as file1) with 10 records having the code at last taken from file 2
Code'd |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
I'm going to assume for now that you have SyncSort. If you do have DFSORT, post the sysout from the step so we can see your software level.
SyncSort questions lives in the JCL forum.
I have Code'd your post, again. Please learn how to use the Code tags.
It seems you have no use of the records with space from File 2. So, add OMIT=(start,2,CH,EQ,C' ') to your JOINKEYS for F2.
You (SyncSort) don't have KEYBEGIN. Your BEGIN is making a group for every record.
Use IFTHEN=(WHEN=INIT to append a sequence number with RESTART=(9,7). For now, as you only seem to have a single value or space on the F2, forget the GROUP.
For OUTFIL, use INCLUDE=( to only keep those with a sequence number of one. |
|
| Back to top |
|
|
rajendra kalepu
New User
Joined: 29 Jul 2011
Posts: 21
Location: India
|
|
|
|
Hi Bill,
Sorry for the late reply.
Your inputs helped a lot in achieving desired results. Especially the grouping and use of PUSH key word.
Thank you very much |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
