Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to filter Duplicate Records
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] How to filter Duplicate Records
 Login/Join
 
Member
posted
Howdy All



So, I'm new to WebFOCUS and programming in general, basic training is in progress.

That being said, this is the second time I have come across the following scenario, and have not been able to find a solution (or a concise way to state the problem):



I have a table that lists employee education information as follows:



Employee_ID

Employee_Name

Degree

University_Name

Date_Awarded

(About 50 other fields relating to education and training info).



The question I am asking is: What are the email addresses and degrees of employees who attended "UCLA"?



The client wants the report in the following format (I am joining to a table showing active employee data by Employee ID to get email addresses):



Employee_ID

Email Address

Degree,

Date Awarded





The problem is, my report keeps giving me duplicates for alums of multiple programs (An employee that attended undergrad, grad and certification will have three different records).



For some reason 'Distinct' and 'Last" eliminate some of the dupes, but not all.



Any direction would be appreciated.

This message has been edited. Last edited by: CSun,


WF 7.7.03 on Win7/Tomcat
 
Posts: 4 | Registered: April 28, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Could use SET command

SET ALL=OFF


WebFocus 8104,8204
Excel/PDF/HTML/HTMLTABLE/XML/ALPHA/GIF file/GRAPH/Active technologies
 
Posts: 182 | Location: INDIA | Registered: July 11, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Put all fields as BY's and you will not have duplicates in the report.

Nevertheless you could still get multiple records for employees that attended multiple programs in the same university. In that case you do need to use MAX or similar for last Date Awarded. I am not sure how to determine which is last Degree. Depends on your data.

Martin.


WebFocus 8203M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 154 | Registered: March 29, 2013Reply With QuoteReport This Post
Master
posted Hide Post
My guess that the reason you are getting duplicates in some cases but not others is that the university names are not entered in a consistent manner. So UCLA is not the same as UCLA_ (where _ represents a space at the end).

What you should do is initially create a table that is sorted by university, then degree etc. That should immediately identify if you have incorrectly entered data.

Once you are sure you have good data, HOLD this initial pass and then re-sort using the distinct attribute.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Reply With QuoteReport This Post
Member
posted Hide Post
The problem is the data you are trying to combine. The data allows 2 places for duplicates Degree and Date Awarded. One way to get a clean list would be to build a HOLD file with EmpID, Email, and LAST(Date Awarded). Then join the hold file back to the employee table on ID and Date, then bring in Degree and you will get the last degree.


WebFOCUS 8
Windows, All Outputs
 
Posts: 1 | Location: Chicago IL | Registered: June 25, 2015Reply With QuoteReport This Post
Member
posted Hide Post
Thanks, for the quick responses on this.

Printing all of the fields as BY's still showed several duplicates. The only accurate way to get last degree was to separate them into levels (BS, MS, etc.).

Mmally's idea of creating a Hold file with ID and date worked perfectly.

Thanks again!


WF 7.7.03 on Win7/Tomcat
 
Posts: 4 | Registered: April 28, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to filter Duplicate Records

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.