Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Matching whole key from one file to any position in other
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
southee

New User


Joined: 17 Jun 2012
Posts: 20
Location: INDIA

PostPosted: Sat Aug 04, 2012 12:46 pm    Post subject: Matching whole key from one file to any position in other
Reply with quote

Hi,

I have two files FILEA and FILEB and their details are as below

FILEA:-

Code:
FIELD1(9)       FIELD2(40)                   FIELD3(15)     
123456789     BAKER                          0000000000000123
123456799     TIM                            0000000000000123


FIELB:-
Code:
FIELD1(9)        FIELD2(40)                 FIELD3(20)   
123456789      JOSEPH BAKER            100750000000000000123
123456799      TIM  MENDAHIL           100750000000000000123



Now i need to match FILEA with FILEB on FIELD2 .
FIELD2 in in FILEA is short name where as FIELD2 in FILEB is Full name.

Is tthere a way to specify IN dfsort so that whole key from FILEA will be Matched with anyposition in FILEB


I woul like to have all the records from two files.

Both the files are FB with record length of 80.

Code'd and aligned finally
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Aug 04, 2012 1:01 pm    Post subject:
Reply with quote

during INREC of FILEB,
if you parse the field2 into two new fields of 40 and 40
you could then JOINKEYS with field2 of filea and the new field2 of fileb.

That way during your format you can refer to the original field2 of fileb.
Back to top
View user's profile Send private message
southee

New User


Joined: 17 Jun 2012
Posts: 20
Location: INDIA

PostPosted: Sat Aug 04, 2012 1:27 pm    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
during INREC of FILEB,
if you parse the field2 into two new fields of 40 and 40
you could then JOINKEYS with field2 of filea and the new field2 of fileb.

That way during your format you can refer to the original field2 of fileb.


Thank you very much for your quick reply.
The number of words in Full name might be more than two words. This way based on the number of words i need to parse full name dynamically which in turn i need to increase the Record length greater than 80

What if i have more than 2 words in full name like
MR JOSEPH BAKER II

This way i need to parse this into 4 fields of 40 length and need to increase record length.

Is there a way where i can dynimaccly parse and increase LRECL dynamically to satisfy above method.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Aug 04, 2012 2:58 pm    Post subject:
Reply with quote

well, I assumed (my error, i realize) that the first part of the name field was the short name.

using inrec build you can essentially 'dynamically' increase the length
and outrec/outfil build you can 'dynamically' decrease the length.
(you really should remove the word 'dynamically' from you vocabulary)

what is field1 in both files?
if this is some kind of account number, you could use that for JOINKEYS and then IFTHEN yourself into oblivion.

you should look at the parse instruction in the manual.
I have seen examples in the forum where the return was zero length.
(i.e. nothing to parse)

if the shortname (field2 of filea) is really capable of being any portion of the long name (field2 of fileb)
yes, this exercise could be fun.

keep in mind, in sort,
you can not compare a value in one record to a value in another record.
if there is someway to join the records (field1?)
you could then do your compare of field2-filea to substring of field2-fileb.

at this point, i am at the end of my suggestions,
somebody else may come along this weekend and add theirs.

good luck.
Back to top
View user's profile Send private message
knickraj
Warnings : 1

New User


Joined: 11 Jun 2007
Posts: 50
Location: Euro

PostPosted: Sat Aug 04, 2012 6:02 pm    Post subject:
Reply with quote

You may try this..
here I am assuming your FIELD1(9) in both the files are assigned to same person i.e

123456789 BAKER
123456789 JOSEPH BAKER

you may build a dynamic include statement from file A using a sort in a temp file.

Code:
 outfil removecc,
 header( INCLUDE COND=((Dummy condition for syntax),OR,   
 Build ( (11,40,SS,EQ,C'BAKER'),OR,     
 Trailer((Dummy condition for syntax ))


you will get a o/p file like for all the records in file A
Code:

INCLUDE COND=(dummy condition ),OR
(11,40,SS,EQ,C'BAKER'),OR,     
(11,40,SS,EQ,C'TIM'),OR,
 (11,40,SS,EQ,C'XX'),OR


use this temp file as sortcard sysin (inlclude) for sorting with file B to another temp file .

now you can join on file A and this second temp file on FIELD1(9)
123456789

This also wont give correct result, if a short name is someone else first name, e.g a full name as TIM BAKER, or BAKER TIM
Back to top
View user's profile Send private message
knickraj
Warnings : 1

New User


Joined: 11 Jun 2007
Posts: 50
Location: Euro

PostPosted: Sat Aug 04, 2012 8:43 pm    Post subject: Reply to: Matching whole key from one file to any position i
Reply with quote

CORRECTION:

instead of include COND you may use IFTHEN overlay

Code:
IFTHEN=(WHEN=(11,40,SS,EQ,C'BAKER'),                         
  OVERLAY=(81:C'BAKER')


this would pull out matching short name from full name, the file may look like

Code:

123456789 JOSEPH BAKER 100750000000000000123     81:BAKER
123456799 TIM MENDAHIL 100750000000000000123     81:TIM


then it can be joined with file A, using all fields and short name,exclluding full name.
Back to top
View user's profile Send private message
southee

New User


Joined: 17 Jun 2012
Posts: 20
Location: INDIA

PostPosted: Sat Aug 04, 2012 9:07 pm    Post subject: Re: Reply to: Matching whole key from one file to any positi
Reply with quote

knickraj wrote:
CORRECTION:

instead of include COND you may use IFTHEN overlay

Code:
IFTHEN=(WHEN=(11,40,SS,EQ,C'BAKER'),                         
  OVERLAY=(81:C'BAKER')


this would pull out matching short name from full name, the file may look like

Code:

123456789 JOSEPH BAKER 100750000000000000123     81:BAKER
123456799 TIM MENDAHIL 100750000000000000123     81:TIM


then it can be joined with file A, using all fields and short name,exclluding full name.



Hi,

I can use field1 in both files to take the matched records but at the same time i need to take those records only where in the condition " compare of field2-filea to substring of field2-fileb. " has to be satisfied.

As sample i provided only two records for understanding purpose but there are some lakhs of records.


knickraj i couldn't understand the steps you mentioned for above problem. could you please eloborate. Sorry for that.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Aug 04, 2012 9:58 pm    Post subject:
Reply with quote

i can't help but feel that I am missing something
(as well as apologizing for re-entering the conversation).

does the short name field only contain one (1) word?
word is defined as a string of characters bounded by one of the following:
  • Begining of field and space
  • space and space
  • space and End of field

If there is indeed only one (1) word to be found in short name,
then continue reading,
else disregard the rest of this post


JOINKEYS on acct no - field1 of both filea and fileb.

SS can not be use because there are too many records to create the constant needed for the SS construct.

so, parse the short name field so that it contains no leading or trailing spaces.
parse the long name field into 20 parse units also so that none contain leading or trailing spaces.

outrec INCLUDE if short name parse eq to any of the 20 long name parses.
Back to top
View user's profile Send private message
southee

New User


Joined: 17 Jun 2012
Posts: 20
Location: INDIA

PostPosted: Sat Aug 04, 2012 10:12 pm    Post subject:
Reply with quote

dbzTHEdinosauer ,

Your solution will work perferct like charm if my short name contains only one word.

But my file also contains (Only few in those lakhs of records) greater than one word.

Thanks for your Idea this would work really fine for one word in my short name. But my short name contains two words like
Baker II(for e.g.) in some records.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Sun Aug 05, 2012 1:34 am    Post subject: Reply to: Matching whole key from one file to any position i
Reply with quote

If you have two "words", parse them into individuals like the other. Word1 will always precede Word2. So, if Word2 not blank, do the match on pairs, else singly.
Back to top
View user's profile Send private message
southee

New User


Joined: 17 Jun 2012
Posts: 20
Location: INDIA

PostPosted: Sun Aug 05, 2012 1:53 pm    Post subject: Re: Reply to: Matching whole key from one file to any positi
Reply with quote

someway to join the records (field1?)
you could then do your compare of field2-filea to substring of field2-fileb.


dbZ,

If i join two files with FIEL1 as key how could i compare of field2-filea to substring of field2-fileb.
Back to top
View user's profile Send private message
knickraj
Warnings : 1

New User


Joined: 11 Jun 2007
Posts: 50
Location: Euro

PostPosted: Sun Aug 05, 2012 2:44 pm    Post subject:
Reply with quote

Hello,
even parse seems tricky, coz your Short name and full name is/are same length 40.

even if you parse to the short name string with STARTAT=string or ENDAT=String,or something like that to remove leading trailing spaces ,
what you would give the FIXLEN value, as your short name
can go upto 40 which is the length, also for the full name .

Another way may be to SQZ right and then Compare byte by byte.. icon_rolleyes.gif
but still...how to compare from which byte to which byte.

Is there any specific reason for both short name and full name to be of length 40?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Sun Aug 05, 2012 5:21 pm    Post subject: Reply to: Matching whole key from one file to any position i
Reply with quote

Why would length be a problem? If you give FIXLEN=40, can contain zero to 40 non-space bytes, the number depending on the rest of the PARSE.

I do think we need more details of the requirement, why if a number is available is there a match on names needed?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sun Aug 05, 2012 5:35 pm    Post subject:
Reply with quote

Bill,

for some reason, the TS wants to differentiate between those accounts
where the short name is a subset of long name
and those
where the short name is not part of the long name.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Sun Aug 05, 2012 5:53 pm    Post subject: Reply to: Matching whole key from one file to any position i
Reply with quote

time to lock the topic,
there is no utility solution until the TS tells which <word> of the <name> constitutes the short name ...
in the first example it is BAKER ( second <word> ) in the second it is TIM ( first <word> )

until the <word> chosen will be clearly identified we are just wasting time icon_evil.gif
Back to top
View user's profile Send private message
southee

New User


Joined: 17 Jun 2012
Posts: 20
Location: INDIA

PostPosted: Sun Aug 05, 2012 6:18 pm    Post subject: Re: Reply to: Matching whole key from one file to any positi
Reply with quote

enrico-sorichetti wrote:
time to lock the topic,
there is no utility solution until the TS tells which <word> of the <name> constitutes the short name ...
in the first example it is BAKER ( second <word> ) in the second it is TIM ( first <word> )

until the <word> chosen will be clearly identified we are just wasting time icon_evil.gif


The Full name consists of

Title(like MR,DR,.......)+First name+Middle Name+Last Name

Where as the Short name is any TITLE(IF available) + (Any of the First name+Middle Name+Last Name)
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Sun Aug 05, 2012 6:44 pm    Post subject: Reply to: Matching whole key from one file to any position i
Reply with quote

the last description does not match the initial description of the requirement!

since we are not dentist for what reason getting reasonable and clear info is like pulling a tooth ? icon_evil.gif

repost a COMPLETE description of the requirement with a proper sample of the data
long name , short name , honorific/title ( Mr, Mrs, Dr. ... )
if the honorific/title cannot be parsed by comparison Your requirement DOES NOT HAVE a solution
Back to top
View user's profile Send private message
southee

New User


Joined: 17 Jun 2012
Posts: 20
Location: INDIA

PostPosted: Sun Aug 05, 2012 6:59 pm    Post subject: Re: Reply to: Matching whole key from one file to any positi
Reply with quote

enrico-sorichetti wrote:
the last description does not match the initial description of the requirement!

since we are not dentist for what reason getting reasonable and clear info is like pulling a tooth ? icon_evil.gif

repost a COMPLETE description of the requirement with a proper sample of the data
long name , short name , honorific/title ( Mr, Mrs, Dr. ... )
if the honorific/title cannot be parsed by comparison Your requirement DOES NOT HAVE a solution


Enrico,

SINCE there are lakhs of records with different permutations it is difficult to tel all the records structre.

I will try with solutions given by dBZ of parsing full name and comparing them with the short name using or clause.

Thank you very much for the soluion dbZ . I will post the code if i'm successful.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Mon Aug 06, 2012 1:00 am    Post subject: Reply to: Matching whole key from one file to any position i
Reply with quote

Field1 can be used for a match. Why would the names be different, other than the short vs long.

What I was trying to say before is that if the short name contains two (or more) elements, there is no problem with parsing each element to 40 bytes, even though the source field is only 40 bytes (or whatever size).

If, like "BAKER II", the elements appear in order and consecutively in the long name, then the match can be done reasonably easily.

However, if "MR BAKER" is expected to match "MR JOHN BAKER" then the match becomes more problematic, in that there will be a large number of IFTHENs. The IFTHENs can be generated, but you have to know the limits of elements in the long and short names.

Before 5pm, European time, you need to have an exact explanation of how you want it to work with a full set of input samples and expected output. That will give Kolusu a chance to look at it without a lot more toing-and-froing.

If you get to something yourself, that will be very refreshing and you'll have our heartfelt congratulations.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Aug 06, 2012 1:29 am    Post subject:
Reply with quote

Hello,

Quote:
it is difficult to tel all the records structre.
And this is some reason to be imcomplete. . . icon_sad.gif

How long do you intend to "discover" data patterns that also need consideration. . .?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us