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     [CLOSED] How to filter duplicate values in a Report

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] How to filter duplicate values in a Report
 Login/Join
 
Amit Somaru
Member
posted
Hi,

I have a report that when run it comes up with duplicate values for the same transaction. How can i filter that out?

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


v7.6, Windows 2003, MS SQL 2003
 
Posts: 12 | Location: Mississauga | Registered: December 22, 2008Report This Post
Francis Mariani
Expert
posted Hide Post
Would SUM instead of PRINT be the answer you're looking for?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Amit Somaru
Member
posted Hide Post
Hi,

I tried that, it sums up all the values, i don't want that.

My report is looking for purchases over $25,000 on a day to day basis, this is what i get when i run the report.

ORDER_DIVISION SUPPLIER_NUMBER SUPPLIER_NAME PURCHASE_ORDER_NUMBER BUYER_CODE ORDER_DATE GROSS_AMOUNT
O 02205 BELDEN CANADA INC. 3 76606 2009/07/03 93733.2200
O 02205 BELDEN CANADA INC. 3 76606 2009/07/03 93733.2200
O 02205 BELDEN CANADA INC. 3 76606 2009/07/03 93733.2200
O 02205 BELDEN CANADA INC. 3 76606 2009/07/03 93733.2200
O 02205 BELDEN CANADA INC. 3 76606 2009/07/03 93733.2200
O 02205 BELDEN CANADA INC. 3 76606 2009/07/03 93733.2200


v7.6, Windows 2003, MS SQL 2003
 
Posts: 12 | Location: Mississauga | Registered: December 22, 2008Report This Post
RSquared
Guru
posted Hide Post
Are you doing any joins?

Please post your code.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Amit Somaru
Member
posted Hide Post
TABLE FILE FCT_PURCH
PRINT
'FCT_PURCH.FCT_PURCH.ORDER_DIVISION'
'FCT_PURCH.FCT_PURCH.BRANCH_CODE'
'FCT_PURCH.FCT_PURCH.SUPPLIER_NUMBER'
'FCT_PURCH.FCT_PURCH.SUPPLIER_NAME'
'FCT_PURCH.FCT_PURCH.PURCHASE_ORDER_NUMBER'
'FCT_PURCH.FCT_PURCH.BUYER_CODE'
'FCT_PURCH.FCT_PURCH.ORDER_DATE'
'FCT_PURCH.FCT_PURCH.GROSS_AMOUNT'
HEADING
""
FOOTING
""
WHERE FCT_PURCH.FCT_PURCH.GROSS_AMOUNT GT 25000;
WHERE FCT_PURCH.FCT_PURCH.ORDER_DATE EQ 20090703;


v7.6, Windows 2003, MS SQL 2003
 
Posts: 12 | Location: Mississauga | Registered: December 22, 2008Report This Post
Francis Mariani
Expert
posted Hide Post
Is there a join that is causing the duplicate rows or is it normal that duplicate rows exist in the data?

This may be one possible solution:

TABLE FILE FCT_PURCH
SUM
FST.FCT_PURCH.FCT_PURCH.GROSS_AMOUNT
BY LOWEST 1 FCT_PURCH.FCT_PURCH.ORDER_DIVISION
BY LOWEST 1 FCT_PURCH.FCT_PURCH.BRANCH_CODE
BY LOWEST 1 FCT_PURCH.FCT_PURCH.SUPPLIER_NUMBER
BY LOWEST 1 FCT_PURCH.FCT_PURCH.SUPPLIER_NAME
BY LOWEST 1 FCT_PURCH.FCT_PURCH.PURCHASE_ORDER_NUMBER
BY LOWEST 1 FCT_PURCH.FCT_PURCH.BUYER_CODE
BY LOWEST 1 FCT_PURCH.FCT_PURCH.ORDER_DATE
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Amit Somaru
Member
posted Hide Post
Hi,

There is no Join on this report, it's normal that duplicate rows exists for the data. is there a way to remove the duplicate?


v7.6, Windows 2003, MS SQL 2003
 
Posts: 12 | Location: Mississauga | Registered: December 22, 2008Report This Post
Francis Mariani
Expert
posted Hide Post
How about my suggestion above?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Amit Somaru
Member
posted Hide Post
It works, but it only show one transaction, i should have 2 transaction for that date.


v7.6, Windows 2003, MS SQL 2003
 
Posts: 12 | Location: Mississauga | Registered: December 22, 2008Report This Post
ac1
Member
posted Hide Post
quote:
Originally posted by Amit Somaru:
It works, but it only show one transaction, i should have 2 transaction for that date.


Why do you say that you have 2 transactions for that date?
Which is the field that can identify the two transactions?



FOCUS 7.2.0S1
Mainframe - MVS 390
Excel, Flat file

Italy
 
Posts: 25 | Registered: June 23, 2009Report This Post
Francis Mariani
Expert
posted Hide Post
My suggested code will give you one report row for the set of BY LOWEST 1 columns. From the example data you posted, it appears that there should only be one row as output.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
PBrightwell
Master
posted Hide Post
quote:
BY LOWEST 1 FCT_PURCH.FCT_PURCH.PURCHASE_ORDER_NUMBER


Francis is on the right track, but his code will always give you only one record. I have the feeling that the above line is the unique identifier for the transaction. It is the only one you will need the "BY LOWEST1" on. Try removing it from all of your other BY statements.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Doug
Expert
posted Hide Post
Instead of:
TABLE FILE FCT_PURCH SUM FST.FCT_PURCH.FCT_PURCH.GROSS_AMOUNT
BY LOWEST 1 FCT_PURCH.FCT_PURCH.ORDER_DIVISION
...
BY LOWEST 1 FCT_PURCH.FCT_PURCH.ORDER_DATE
END
could you do something like:
TABLE FILE FCT_PURCH SUM FST.FCT_PURCH.FCT_PURCH.GROSS_AMOUNT
WHERE FCT_PURCH.FCT_PURCH.ORDER_DIVISION NE LAST FCT_PURCH.FCT_PURCH.ORDER_DIVISION
...
WHERE FCT_PURCH.FCT_PURCH.ORDER_DATE NE LAST FCT_PURCH.FCT_PURCH.ORDER_DATE
END




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 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     [CLOSED] How to filter duplicate values in a Report

Copyright © 1996-2020 Information Builders