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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] How to filter Duplicate Records
 Login/Join
 
CSun
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, 2015Report This Post
Neelima
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: 186 | Location: INDIA | Registered: July 11, 2013Report This Post
Martin vK
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 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 168 | Registered: March 29, 2013Report This Post
George Patton
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, 2010Report This Post
mmally
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, 2015Report This Post
CSun
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, 2015Report 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     [SOLVED] How to filter Duplicate Records

Copyright © 1996-2020 Information Builders