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 need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
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
 
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
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
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
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
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
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
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
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
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
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
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
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
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