Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you have any questions or need access: Contact myibi@ibi.com


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Character check loop?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Character check loop?
 Login/Join
 
Platinum Member
posted
I was just wondering if there is a function that can check characters for duplicates. For example: I have a file with 150 records of ssn's, first name, lastname, address, dog, cat, anut, uncle, salary... and so forth. tons of data.

What I want to do is run through the file and make sure there is only 1 ssn (other wise we would have 2 records exatly the same).

Is there a looping function that can go through each row of a file and check that? Can this be done in a define without the use of a hold file?
 
Posts: 178 | Registered: May 11, 2005Report This Post
Expert
posted Hide Post
Why not count the SSN's by SSN and print a report where the count is greater than one?Here's an example using the CAR file. I am using the SEATS field.
First run the report with the WHERE clause commented, then run it uncommented to see the difference.
TABLE FILE CAR
SUM
CNT.SEATS
BY SEATS
PRINT>COUNTRY
CAR BY SEATS
BY MODEL
*WHERE TOTAL CNT.SEATS GT 5
END

This message has been edited. Last edited by: <Mabel>,
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
That's not a bad idea. I'm wondering if I can do that in a define.

SSN_CHECK/I9 = CNT.SSN;
SSN_NUM/I9 = If SSN_CHECK GT 1 then ERROR else ' ';

Or something like that... you know?

Thanks for your help. Not sure if my defines will work, but you have pushed me in the right direction.
 
Posts: 178 | Registered: May 11, 2005Report This Post
Guru
posted Hide Post
DEFINES are calculated for each individual row.
If the first DEFINE didn't give you errors the value would always be 1.

Do you want to list all of the SSN's on the report?

If not, then do what Francis advised. The WHERE TOTAL says do the test after you add up the counts. This will list only the SSN's that appear more than once.

SUM CNT.SSN
BY SSN
WHERE TOTAL CNT.SSN GT 1

If you want to see all the SSN's then you don't need to do the WHERE. USe conditional styling to make the ones with dups stand out.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
Expert
posted Hide Post
Hi, are those suggestions helpful to you? Is this still an outstanding issue? Did you try the search on Focal Point?

Please check the following document and see if this is related to what you are looking for.

HOW TO LOCATE DUPLICATE RECORDS
http://techsupport.informationbuilders.com/sps/81071020.html

You will need to logon in order to access this document.

Hope this helps. Big Grin

Cheers,

Kerry
 
Posts: 1950 | Location: New York | Registered: November 16, 2004Report This Post
Platinum Member
posted Hide Post
Those suggestions are very helpful. I was trying to think of a way to do it in a define, but I am now actually using a whole report to do a count, so I think what was suggested will work. Thanks so much!!
 
Posts: 178 | Registered: May 11, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Character check loop?

Copyright © 1996-2020 Information Builders