IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Finding repetead surnames in differents colmuns


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
blackjack202

New User


Joined: 24 May 2016
Posts: 4
Location: Spain

PostPosted: Tue Jun 07, 2016 7:42 pm
Reply with quote

Hi there,

I have the following problem. I will try to explain it, the idea is to delete those records who have relatives in the same oficce. We know that if the surname is repeat it, we have to remove those records. But the big problem, at least for me, is how can i manage when the surname1 is also in the surname 2 or the other way around?

For example:

Format INPUT ==> OFIC (1 CHAR) - ACC.NUM( 1 CHAR) - SURNAME1 (50 CHAR) - SURNAME2 (50 CHAR) - NAME (50 CHAR)
INPUT:
Code:

OFIC.   ACC.NUM      SURNAME1    SURNAME2   NAME
1          1         ARIZONA     ROCAFORT   JUAN
           1         ROCAFORT    MARTINEZ   ANNA
           2         SEVILLA     RODRIGUEZ  ERNESTO
           3         ARIZONA     MARTINEZ   JULIAN           
2          1         RODRIGUEZ   MARTINEZ   FEDERICO
           2         RODRIGUEZ   MARTINEZ   FAUSTO
           3         AMARAL      ZAPATA     GILDA
           3         BAGGINS     SHIRE      ROBERT
           4         ZAPATA      TANK       JOHN
3          1         MARTIN      MERIADOC   MARCOS
           1         CRYSTAL     EDURNE     VERONICA

OUTPUT:

Code:
OFIC.   ACC.NUM      SURNAME1    SURNAME2   NAME
1          2         SEVILLA     RODRIGUEZ  ERNESTO
2          3         BAGGINS     SHIRE      ROBERT
3          1         MARTIN      MERIADOC   MARCOS
           1         CRYSTAL     EDURNE     VERONICA

The most complicated case, as you can see, is the case of the OFIC = 2 ACC. NUM = 3 and 4, the SURNAME ZAPATA is in column SURNAME1 and SURNAME2. It is possible to do this with DFSORT or any other? am open to any suggestion. For me will be easy to do it with an internal table in COBOL, but is not possible for me to reach this solution for burocratic stuff here at work.

Thanks you so much, for your support! icon_biggrin.gif

PD: Yes, it is my first time, in the fórum icon_redface.gif
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Jun 07, 2016 8:36 pm
Reply with quote

Welcome!
May be, with push and group with SS and IFTHEN conditions.
Back to top
View user's profile Send private message
blackjack202

New User


Joined: 24 May 2016
Posts: 4
Location: Spain

PostPosted: Tue Jun 07, 2016 8:41 pm
Reply with quote

Hi Rohit,

The record with the SURNAME1 is AMARAL, but the second SURNAME is ZAPATA, the same as in the ACC. NUM = 2, who had the SURNAME1 = ZAPATA, and they belong to the same OFIC = 2, thats why are deleted.

BTW, thnks for the welcome ;)

Regards!
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Tue Jun 07, 2016 8:41 pm
Reply with quote

Because it is Amaral Zapata? and there is a Zapata in surname 1 further down.
Back to top
View user's profile Send private message
blackjack202

New User


Joined: 24 May 2016
Posts: 4
Location: Spain

PostPosted: Tue Jun 07, 2016 9:09 pm
Reply with quote

Hi,

@Nic Clouston: Exactly, is repetead 1 further down ;)

@Rohit: Would you mind give me more 'light' on this? As far as i know, the SS (Sub String i imagine), how can handle in a GROUP? I mean, the SS, i can get the first SURNAME1 and i can read the second column SURNAME2, but when i read the record2 of the SURNAME1, how i can do it?

Not sure if i can explain it very well. Sorry if my question is silly.

I appreciate it the help and the idea ;)

Regards
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Jun 07, 2016 10:53 pm
Reply with quote

Are you sure you just want to get rid of things dependent on a match of one out of two surnames? I thought many Spanish surnames were common even when not related?

Anyway, to do it, use JOINKEYS specifying the same file for the input, with the two different key-positions (remembering that office must be first).

That still leaves you with knowing internal duplicates within the two "files".

Before going into that, can you confirm about the requirement. It seems a bit silly. It seems far more likely that a common surname in an office is not of a relative, except for distant ones. Why would you want to do this, what's the business reason?
Back to top
View user's profile Send private message
blackjack202

New User


Joined: 24 May 2016
Posts: 4
Location: Spain

PostPosted: Wed Jun 08, 2016 12:24 am
Reply with quote

Hi Bill,

That is 100% correcto icon_smile.gif . In Spain, have the same surname dont made us relatives. The only why, is that confirm when go to bank that this person is relative with another (even do, don't have any sense). But well, the client (the bank) want this report, knowing that the people with the same surname don't made it relative. Here in Spain we say "Donde manda Capitan, no manda Marinero", something like "Where a captain rules, a sailor has no sway" icon_sad.gif
The requirement, is because, (you will laught) they dont want that we use internat tables in COBOL programs, just in case "we receive a migration of another bank, and the table collapse" (yes, i put the same face as you ) icon_eek.gif
I think i get the idea from the JOINKEYS, i will try to do it in the following days and i will post it again if i got some logical troubles!

Thank you, so much! icon_biggrin.gif & regards
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Jun 08, 2016 1:33 am
Reply with quote

Great fun, then.

Since you have to SORT the data, into two sequences, extend the records to include a "count" field (at the beginning for variable-length records, at the end for fixed-length records) which is large enough for the maximum possible people dealing with the same office. Make it a factor of 10 larger, just in case there's a bank merger :-)

SUM on the count.

For the JOIN use UNPAIRED,F1,ONLY.

In the Maintask of the JOINKEYS, use INCLUDE= for the count being one, and have a BUILD on INREC to drop off the count you added earlier.

If you then need some other order than first-surname, you can SORT in the Maintask as well.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts VB to FB - Finding LRECL SYNCSORT 4
No new posts Finding Assembler programs PL/I & Assembler 5
No new posts Finding faulty logic Subscript out of... COBOL Programming 5
No new posts Finding record and replacing with val... DFSORT/ICETOOL 3
No new posts Finding a semi colon (;) in ISPF TSO/ISPF 5
Search our Forums:

Back to Top